VBA hiding rows seems slow any ideas?

[KirnonBhale]

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub HideRowsOnSheet()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim i As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For i = 10 To 122[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    IfActiveSheet.Cells(i, "D") = 0 And ActiveSheet.Cells(i, "D")<> "" Then ActiveSheet.Rows(i).Hidden = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

The above code does the job but it takes over 2min. to finish running. This seems especially slow as I could accomplish the task that the code is performing more quickly. Is there a quicker way to accomplish the task of hiding rows with a 0 in column D?

I have a similar code for operation over 8 Tabs but it takes 20 min + to fully run which is in my mind excessive.

Any help making this run quicker would be greatly appreciated.

 

[Fluff]

You could use the Autofilter to do that.
Simply record yourself applying the filter & you have the code.

 

[sykes]

What about if you only did one test i.e. for a string of "0" rather than = 0 AND <> 0? :

Sub HideRowsOnSheet()
Dim i As Long
Application.ScreenUpdating = False
For i = 10 To 122
    IfActiveSheet.Cells(i, "D") = "0"  Then ActiveSheet.Rows(i).Hidden = True
Next i
Application.ScreenUpdating = True
End Sub

 

[steve the fish]

Maybe try this. It doesnt access the worksheet so much and does all of the hiding at once:

Sub HideRowsOnSheet2()

Application.ScreenUpdating = False

Dim fr As Long, lr As Long, arr, i As Long, rng As Range

fr = 10
lr = 122
col = 4

Range(Cells(fr, 1), Cells(lr, 1)).EntireRow.Hidden = False

arr = Range(Cells(fr, col), Cells(lr, col))

For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + fr - 1, col))
        Else
            Set rng = Cells(i + fr - 1, col)
        End If
    End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

 

[KirnonBhale]

Maybe try this. It doesnt access the worksheet so much and does all of the hiding at once:

Sub HideRowsOnSheet2()

Application.ScreenUpdating = False

Dim fr As Long, lr As Long, arr, i As Long, rng As Range

fr = 10
lr = 122
col = 4

Range(Cells(fr, 1), Cells(lr, 1)).EntireRow.Hidden = False

arr = Range(Cells(fr, col), Cells(lr, col))

For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + fr - 1, col))
        Else
            Set rng = Cells(i + fr - 1, col)
        End If
    End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

Wow!!! Steve_The_Fish - that is super quick. Exactly what I needed.

Thanks for your suggestion Fluff and sykes.

sykes - the blank cells were being included in the hide when I didn't include the 2nd test.

Fluff - I don't know how to have autofilter in VBA reference the array as excluded value only rather than array included values. So i don't think that would work as well.

 

[Fluff]

Fluff - I don't know how to have autofilter in VBA reference the array as excluded value only rather than array included values. So i don't think that would work as well.

You do it like

Sub KirnonBhale()
   Range("A10:D122").AutoFilter 4, "<>0"
End Sub

 

[KirnonBhale]

You do it like

Sub KirnonBhale()
   Range("A10:D122").AutoFilter 4, "<>0"
End Sub

Thanks Fluff. i'll keep that nugget for another day.

 

[Fluff]

You're welcome

 

[KirnonBhale]

Maybe try this. It doesnt access the worksheet so much and does all of the hiding at once:

Sub HideRowsOnSheet2()

Application.ScreenUpdating = False

Dim fr As Long, lr As Long, arr, i As Long, rng As Range

fr = 10
lr = 122
col = 4

Range(Cells(fr, 1), Cells(lr, 1)).EntireRow.Hidden = False

arr = Range(Cells(fr, col), Cells(lr, col))

For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + fr - 1, col))
        Else
            Set rng = Cells(i + fr - 1, col)
        End If
    End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

Just needed to say - how impressed I am by this - I wish I was able to code like this. My coding is so clunky and cumbersome in comparison.

 

[steve the fish]

Its just practice. Im self taught and by using excel, reading around and picking up ideas you learn. In this case you just need to understand that accessing the worksheet and hiding rows is expensive in terms of time so this just gives you a way to do that as little as possible.