How to Filter or Hide Rows & Display Excel Data in Excel Using A Macro

Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010



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:





Home
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010