Macro to Hide Zero Values

[mecerrato]

I am using a working macro to hide all rows that column B is < 0.
The issue is that it runs slow, is there a more efficient or faster way of accomplishing this?
Here is my working macro:

Sub HideZeros()
Dim lrA As Long
StartRow = 10
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
iCol = 2
For i = StartRow To LastRow
If Cells(i, iCol).Value > 0 Then
Cells(i, iCol).EntireRow.Hidden = False
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
End Sub

 

[Michael M]

Is this any faster

Sub HideZeros()
Application.ScreenUpdating = False
Dim LastRow As Long
StartRow = 10
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
iCol = 2
For i = LastRow To StartRow Step -1
If Cells(i, iCol).Value > 0 Then
Cells(i, iCol).EntireRow.Hidden = False
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[mecerrato]

Is this any faster

Sub HideZeros()
Application.ScreenUpdating = False
Dim LastRow As Long
StartRow = 10
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
iCol = 2
For i = LastRow To StartRow Step -1
If Cells(i, iCol).Value > 0 Then
Cells(i, iCol).EntireRow.Hidden = False
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub

Speed is the same

 

[Mushy peas]

You can try using autofilter. I use that all the time to filter my ranges.

Here is an example:

Sub HideZeros()
Dim Filter_rng as Range
Dim iCol as Long
Dim StartRow as Long

StartRow = 10
iCol = 2
Filter_rng = Range(Cells(StartRow - 1, iCol), Cells(Rows.Count, iCol).End(xlUp)
Filter_rng.autofilter Field:=1, Criteria1:=">0"

End Sub

 

[mecerrato]

You can try using autofilter. I use that all the time to filter my ranges.

Here is an example:

Sub HideZeros()
Dim Filter_rng as Range
Dim iCol as Long
Dim StartRow as Long

StartRow = 10
iCol = 2
Filter_rng = Range(Cells(StartRow - 1, iCol), Cells(Rows.Count, iCol).End(xlUp)
Filter_rng.autofilter Field:=1, Criteria1:=">0"

End Sub

This code is giving me an error, I think something is missing:

 

[Mushy peas]

Oh wait, I forgot to add a Set

Sub HideZeros()
Dim Filter_rng as Range
Dim iCol as Long
Dim StartRow as Long

StartRow = 10
iCol = 2
Set Filter_rng = Range(Cells(StartRow - 1, iCol), Cells(Rows.Count, iCol).End(xlUp)
Filter_rng.autofilter Field:=1, Criteria1:=">0"

End Sub

 

[mecerrato]

Oh wait, I forgot to add a Set

Sub HideZeros()
Dim Filter_rng as Range
Dim iCol as Long
Dim StartRow as Long

StartRow = 10
iCol = 2
Set Filter_rng = Range(Cells(StartRow - 1, iCol), Cells(Rows.Count, iCol).End(xlUp)
Filter_rng.autofilter Field:=1, Criteria1:=">0"

End Sub

Still getting an error

 

[Fluff]

Do you have headings in row 9?

 

[Alex Blakenburg]

Still getting an error

That line is missing a closing bracket and s/be

Set Filter_rng = Range(Cells(StartRow - 1, iCol), Cells(Rows.Count, iCol).End(xlUp))

I have not looked at whether it is faster though. You might want to respond to Fluff and I am sure he will be able will be able to provide faster option.

 

[mecerrato]

That line is missing a closing bracket and s/be

Set Filter_rng = Range(Cells(StartRow - 1, iCol), Cells(Rows.Count, iCol).End(xlUp))

I have not looked at whether it is faster though. You might want to respond to Fluff and I am sure he will be able will be able to provide faster option.

It works now, adding the missing bracket and changing the filed ot number 2(my fault) and yes it is much faster, thanks all. I will mark mushypeas as the answer