Runtime Error 1004 - Help

[Dean]

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]

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]

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]

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]

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.