Faster VBA to hide rows

Question

I have a code that checks for each row if the cells in that row are zero (or empty). If a row applies to that rule the row is hidden. If not it remains visible.

The code works perfectly, however it is very very slow (takes about 40 seconds to complete each time I run it)..

I was wondering if anyone could see why my code is slow (or have an alternative which I can use which is faster than my current code)..

 Sub hide()
' Macro hides all rows with position "zero" or "blank"

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim c As Range
    Dim targetRange As Range

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet 1")
    Set targetRange = ws.Range("I10:N800")

    targetRange.EntireRow.Hidden = False

    For Each c In targetRange.Rows
       If (WorksheetFunction.CountIf(c, "<>0") - WorksheetFunction.CountIf(c, "") = 0) And (WorksheetFunction.CountA(c) - WorksheetFunction.Count(c) = 0) Then
       c.EntireRow.Hidden = True
       End If
    Next c


End Sub

Answer 1

The most time consuimng action in your code, is every time you perform actions on your worksheet, in your case when you hide each row (multiple times), here:

c.EntireRow.Hidden = True

In order to save time, every time your condition is met, just add tha range c to a MergeRng, and at the end (when you exit the loop), just hide the entire rows at once.

Try the code below:

Dim MergeRng As Range ' define range object

For Each c In targetRange.Rows
    If (WorksheetFunction.CountIf(c, "<>0") - WorksheetFunction.CountIf(c, "") = 0) And (WorksheetFunction.CountA(c) - WorksheetFunction.Count(c) = 0) Then
        If Not MergeRng Is Nothing Then
            Set MergeRng = Application.Union(MergeRng, c)
        Else
            Set MergeRng = c
        End If
    End If
Next c

' hide the entire rows of the merged range at one time
MergeRng.EntireRow.Hidden = True