Suppose you have the following dataset.
Step 1 – Apply a Filter to the Dataset
- Select any cell of your dataset.
- Go to the Data tab and select Filter.
- Each column can now be filtered.
Read More: Excel VBA to Count Rows with Data
Step 2 – Launch the VBA Window to Count Filtered Rows in Excel
- Go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- In the Visual Basic window, select the Insert Menu and select Module from the drop-down menu.
- The Module window will appear.
Step 3 – Type and Save the Excel VBA Code
- Type this code in the Module window:
Sub Count_Filtered_Rows()
Dim wks As Worksheet
Dim zCount As Long, y As Long
Dim xRng As range
Set wks = ThisWorkbook.Worksheets(2)
zCount = 0
For y = 1 To wks.range("B4").CurrentRegion.Rows.Count
If wks.Cells(y, 1).EntireRow.Hidden = False Then
zCount = zCount + 1
End If
Next y
MsgBox "There are " & zCount & " rows"
End Sub
Note: Fill in the working sheet number between the parentheses in this line: ThisWorkbook.Worksheets(). In this example, that’s Sheet 2. Also fill in the cell where your dataset starts in the parentheses here: wks.range().CurrentRegion.Rows.Count—“B4” in this example.
- Press Ctrl + S to save the code or click the Save icon.
- Close the Visual Basic window.
Step 4: Run the VBA Code to Count Filtered Rows in Excel
- To run the saved code, go to the Developer tab and select Macros.
- Select the code and click Run from the Macro window.
Final Output after Running Excel VBA Code to Count Filtered Rows
- The number of filtered rows (including the header) will show in a pop-up message box.
Example
- Apply a filter to the dataset.
- Go to the Developer tab and select Macros.
- Run the code from the Macro window.
- The results have changed.
- To display the result without the header row, use this code:
Sub Count_Filtered_Rows()
Dim wks As Worksheet
Dim zCount As Long, y As Long
Dim xRng As range
Set wks = ThisWorkbook.Worksheets(2)
zCount = -1
For y = 1 To wks.range("B4").CurrentRegion.Rows.Count
If wks.Cells(y, 1).EntireRow.Hidden = False Then
zCount = zCount + 1
End If
Next y
MsgBox "There are " & zCount & " rows"
End Sub
Use zCount = -1 instead of zCount = 0.
- Press Ctrl + S to save the code.
- Go to the Developer tab and select Macros to Run the code.
Read More: Excel VBA: Count Rows with Specific Data
Things to Remember
- Apply filters before using VBA.
- Use zCount = -1 instead of zCount = 0 to count the row numbers without the header.
- You can also run the saved code by pressing the F5 key.
Download Practice Book
Download the practice book.
Related Articles
- Excel VBA: Count Rows in Named Range
- How to Count Rows in Selection Using VBA in Excel
- Excel VBA: Count Rows in a Sheet
