Script to hide Excel rows where certain columns contain 0 I have the following script which works, but takes a lot of time to run on a worksheet with 2000+ rows. Anyone know of a way to speed it up?

The code runs through the workbook and ignores the pages I do not want it to touch. Then, it runs through any pages that I want it to, looks for rows with a zero in column C and Column D and if found hides the row.

Sub HideDoubleZeors()

Dim LR As Long, i As Long
Dim c As Variant

For Each ws In Worksheets
Select Case ws.Name
    Case "Form1", _
            "Form 2", _
            "Form 3"
            'Do nothing on these tabs

    Case Else 'If not one of the above tab names then do this
    With ws.Activate
        LR = ws.Range("B" &amp; Rows.Count).End(xlUp).Row
        For i = 1 To LR
        With ws.Range("B" &amp; i)
        For Each c In Range("B" &amp; i)
            If c.Value &lt;&gt; "All Forms" _
                And c.Value &lt;&gt; "Week One All Forms" _
                And c.Offset(0, 1).Value = 0 _
                And c.Offset(0, 1).Value &lt;&gt; vbNullString _
                And c.Offset(0, 2).Value = 0 _
                And c.Offset(0, 2).Value &lt;&gt; vbNullString _
            Then Rows(c.Row).Hidden = True
            Next c

       End With
      Next i
   End With
   End Select
Next ws
End Sub
The first rule of optimizing Excel when formatting: "TURN OFF SCREEN UPDATING" 


 Application.ScreenUpdating = False



These line continuations are just ugly.  They add nothing to the readability or functionality of the subroutine.


    Case "Form1", _
        "Form 2", _
        "Form 3"



I would remove the line continuations


   Case "Form1", "Form 2", "Form 3"



There are only a sew cases where it is advantageous to Select or Activate an Object.  This isn't one of them.  Watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)


With ws.Activate



I guess your original code might have needed this line: With ws.Range("B" &amp; i) but now it's just leftover parts.  Remove it.

I hate the old style where you first find the last row and then use it in a loop or Range.  IMO, there isn't a good reason to do this with a simple loop or Range selection.


LR = ws.Range("B" &amp; Rows.Count).End(xlUp).Row
  For i = 1 To LR



What's next..hmm. For Each Cell in My 1 Cell Range????  Have you watched the formationed Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) yet???


For Each c In Range("B" &amp; i)



Range("B" &amp; i) isn't qualified to the ws.  It is referencing the cells of the ActiveSheet. That's probably why you needed to activate the worksheet.

Often it is necessary to unhide all the cells on the worksheet before you reprocess the worksheet.  It probably doesn't apply to this scenario but I thought that I would mention it.

This statement seems somewhat redundant but I'll let someone else handle it.


If c.Value &lt;&gt; "All Forms" _
    And c.Value &lt;&gt; "Week One All Forms" _
    And c.Offset(0, 1).Value = 0 _
    And c.Offset(0, 1).Value &lt;&gt; vbNullString _
    And c.Offset(0, 2).Value = 0 _
    And c.Offset(0, 2).Value &lt;&gt; vbNullString _



Here is the real problem.  You are hiding each row individually.  The preferred method is to create one large Range (per worksheet) and hide all the cells at once.  The easiest way to do this is to use Application.Union.  


Then Rows(c.Row).Hidden = True



The other method collects the range addresses and then creates a range from them.  Although faster, its not worth the extra work to processes a few thousand rows.

Rows(c.Row).Hidden = True works but again its ugly use c.EntireRow.Hidden = True

A better way:

Sub HideDoubleZeors()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Form1", "Form 2", "Form 3"
                'Do nothing on these tabs
            Case Else                                 'If not one of the above tab names then do this
                ProcessWorksheet ws
        End Select
    Next
End Sub

Private Sub ProcessWorksheet(ws As Worksheet)
    Application.ScreenUpdating = False
    Dim cell As Variant, MyRows As Range
    With ws
        For Each cell In ws.Range("B1", .Range("B" &amp; .Rows.Count).End(xlUp))
            If cell.Value &lt;&gt; "All Forms" And cell.Value &lt;&gt; "Week One All Forms" _
               And cell.Offset(0, 1).Value = 0 And cell.Offset(0, 1).Value &lt;&gt; vbNullString _
               And cell.Offset(0, 2).Value = 0 And cell.Offset(0, 2).Value &lt;&gt; vbNullString Then
                If MyRows Is Nothing Then
                    Set MyRows = cell
                Else
                    Set MyRows = Union(MyRows, cell)
                End If
            End If
        Next
    End With

    If Not MyRows Is Nothing Then MyRows.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub


Hopefully the OP gets more out of this than just some free code.  

Good Luck and don't forget to watch that video!!