How to Hide Rows Using VBA in Excel

How to Filter or Hide Rows & Display Excel Data in Excel Using A Macro
Many website visitors have asked how they can hide rows of their Excel data based on a condition using a macro. The best way to achieve a filtering and hiding of rows or data in an Excel worksheet is to use a looping process which checks for a specific condition or conditions. The code below shows how you can check for two conditions and hide specific rows.
The first command button takes care of the looping process and hides the rows that meet the specified condition listed under the ‘IF’ statement. The second command button is used to change the data back to its original state by changing the font color and making all the rows visible.
Private Sub CommandButton1_Click()
Row = 2
Do While Cells(Row, 1) <> “”
If Cells(Row, 2) = “Manager” And Cells(Row, 5) <= 20000 Then

Rows(Row).Hidden = True

End If

If Cells(Row, 2) = “Manager” And Cells(Row, 5) >= 20000 Then
Cells(Row, 2).Font.ColorIndex = 3
End If
Row = Row + 1
Loop
End Sub

Private Sub CommandButton2_Click()
Rows.Font.ColorIndex = 0
Rows.Hidden = False
End Sub

Watch the training video below to learn how to display results of a calculation in a specific way in MS Excel:


Leave a Reply

Your email address will not be published. Required fields are marked *