Excel VBA: Filter (hide) Array using loop

Question

Im still a beginner when it comes to VBA and I'm trying to write a code that hides rows if its cell value equals zero and the user has chosen to set another cell value to the correct wording. However, the code I wrote doesn't seem to work; I'm sure I have forgotten to add something or am missing some key element but can't seem to figure out what it is.

I have posted my code below. Any help would be much appreciated.

Sub HideEmptyRows()

Application.EnableEvents = False

Worksheets("Filtered Data").Rows("7:600").EntireRow.Hidden = False

If Range("J7") = "Filter" Then

    For Each cell In Range("J10:J503")
        If cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        End If
    Next cell

Application.EnableEvents = True

End Sub

You just missed the `End If` – Washington Guedes Jun 21 '18 at 12:44 — Washington Guedes, Jun 21 '18 at 12:44

score 0 · Accepted Answer · answered Jun 21 '18 at 12:47

Consider:

Sub HideEmptyRows()
    Application.EnableEvents = False
    With Worksheets("Filtered Data")
        .Rows("7:600").EntireRow.Hidden = False

        If .Range("J7") = "Filter" Then
            For Each cell In .Range("J10:J503")
                If cell.Value = 0 Then
                    cell.EntireRow.Hidden = True
                End If
            Next cell
        End If

    End With
    Application.EnableEvents = True
End Sub

NOTE:

used With
small clean-up to the logic

score 0 · Answer 2 · answered Jun 21 '18 at 12:48

You don't test to see whether there actually exists a worksheet named "Filtered Data".
You unhide rows in Worksheets("Filtered Data") but then you check values and hide rows in whatever sheet happens to be the ActiveSheet.
You did not declare the variable cell. Option Explicit is a good friend; use it.
There is a missing End If.

Otherwise the code works; tested like this:

Option Explicit

Sub HideEmptyRows()

  Dim cell As Range

  Application.EnableEvents = False

  With Worksheets("Filtered Data")
    .Rows("7:600").EntireRow.Hidden = False
    If .Range("J7") = "Filter" Then
      For Each cell In .Range("J10:J503")
        If cell.Value = 0 Then
          cell.EntireRow.Hidden = True
        End If
      Next cell
    End If
  End With

  Application.EnableEvents = True

End Sub

What would I need to do to alter this code so every time that the "J7" input is changed the code automatically runs and there is no need for a button or a manual Run Sub. — wildesbare, Jun 21 '18 at 15:20
See "[automatically execute an Excel macro on a cell change](https://stackoverflow.com/questions/409434)" on this site. — AlexP, Jun 21 '18 at 15:42

Excel VBA: Filter (hide) Array using loop

2 Answers2