Hi, I am having a little trouble with this code. It produces a runtime
error 1004 in VBA. I think it is coming from this line of code. Would
appreciate any help with this.
Thanks,
Dean
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
Sub Macro4()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Msg = "Please Check You Have Loaded The Label Paper Correctly - " &
vbCr & "Labels Will Be Sent To Printer - All Data Will Be Deleted " &
vbCr & vbCr & "Click OK When You Are Ready To Proceed"
MsgBox Prompt:=Msg
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 5000
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell
ElseIf .Cells(Lrow, "B").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "B")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "B"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
With Application
.ScreenUpdating = True
.Calculation = CalcMode
Application.Run Macro:="Macro5"
rng.EntireRow.Hidden = False
End With
End Sub
idyllicabyss@googlemail.com - 24 Mar 2006 03:41 GMT
I don't think rng is populating properly.
I've tried to run this and rng always = 0, never the range that it
should contain.
I don't know why it doesn't work but I worked around it;
Sub Macro4()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim MyRng As Range
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Msg = "Please Check You Have Loaded The Label Paper Correctly - " &
_
vbCr & "Labels Will Be Sent To Printer - All Data Will Be Deleted " & _
vbCr & vbCr & "Click OK When You Are Ready To Proceed"
MsgBox Prompt:=Msg
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 5000
ZeroCells = 0
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell
ElseIf .Cells(Lrow, "B").Value = "0" Then
'Set MyRng = Application.Union(MyRng, .Cells(Lrow,
"B"))
If ZeroCells > 0 Then
Application.Union(Selection, .Cells(Lrow, "B")).Select
Else
Cells(Lrow, "B").Select
End If
ZeroCells = ZeroCells + 1
End If
Next
End With
'hide all rows in one time
If Not Selection Is Nothing Then Selection.EntireRow.Hidden = True
With Application
.ScreenUpdating = True
.Calculation = CalcMode
Application.Run Macro:="Macro5"
MyRng.EntireRow.Hidden = False
End With
End Sub
Which seems to work.
Dean - 24 Mar 2006 03:47 GMT
Thanks for your reply however still get a runtime error 1004. Any more
Ideas?
Regards,
Dean
Part of code highlighted is:
Selection.EntireRow.Hidden = True
idyllicabyss@googlemail.com - 24 Mar 2006 03:59 GMT
Sorry, it worked when I did it. Perhaps there is some problem when
copying code from here, I had to put some Underscore _ characters in
where code ran over two or more lines.
Have you considered hiding the rows as you find them?
ElseIf .Cells(Lrow, "B").Value = "0" Then
rows(Lrow).entirerow.hidden = true
and then when finished
Cells.EntireRow.Hidden = False
kletcho@gmail.com - 24 Mar 2006 07:54 GMT
If you can distinguish the trigger value (right now it is zero) that
indicates when to hide a row with something more distinct you could use
goto special and do something like this:
Sub Test()
For Each rng In Columns("B:B").SpecialCells(xlCellTypeConstants, 1)
rng.EntireRow.Hidden = True
Next rng
End Sub
This one select on cells that have numbers in them in column B and then
hides them.