VBA: How to Hide Rows Based on Criteria

You can use the following syntax in VBA to hide rows based on a cell value:

Sub HideRows()

    Dim i As Integer
    
    For i = 2 To 10
    
        If Cells(i, 1).Value = "Mavs" Then
            Cells(i, 1).EntireRow.Hidden = True
        Else
            Cells(i, 1).EntireRow.Hidden = False
        End If
        
    Next i

End Sub

This particular macro hides all rows in the range from rows 2 to 10 that have a cell value equal to “Mavs” in the first column.

You can also use the following macro to unhide all rows:

Sub UnhideRows()
    Rows.EntireRow.Hidden = False
End Sub

The following example shows how to use this syntax in practice.

Example: Use VBA to Hide Rows Based on Criteria

Suppose we have the following dataset that contains information about various basketball players:

Suppose that we would like to hide each row that contains “Mavs” in the team column.

We can create the following macro to do so:

Sub HideRows()

    Dim i As Integer
    
    For i = 2 To 10
    
        If Cells(i, 1).Value = "Mavs" Then
            Cells(i, 1).EntireRow.Hidden = True
        Else
            Cells(i, 1).EntireRow.Hidden = False
        End If
        
    Next i

End Sub

When we run this macro, we receive the following output:

Notice that each row that contained “Mavs” in the team column has now been hidden.

If we’d like, we can create the following macro to unhide all rows:

Sub UnhideRows()
    Rows.EntireRow.Hidden = False
End Sub

When we run this macro, we receive the following output:

Notice that all of the rows are now shown again.

Note that in our macro we specified For i = 2 To 10 to hide rows in the range from 2 to 10 based on criteria.

Feel free to change these starting and ending values to hide rows in a different row range.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Apply Conditional Formatting to Cells
VBA: How to Count Number of Rows in Range
VBA: How to Count Cells with Specific Text