Table of Contents
To hide rows based on criteria in excel, you can use the AutoFilter feature to filter out the rows you don’t want to see. To do this, click on the Data tab and select Filter in the Sort & Filter group. From there, you can set the criteria for what rows should be visible. Once the criteria is set, only the rows that meet the criteria will be visible in the spreadsheet.
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
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.