In this article, we will learn to count filtered rows in Excel with VBA. VBA stands for Visual Basic for Applications. It gives us the opportunity to perform various tasks. Sometimes, we need to filter our dataset with various criteria. It helps us to represent the desired data cleanly. Here, we will count the filtered rows using VBA in Excel. We will demonstrate every step so that you can count filtered rows in the Excel worksheet very easily.
Dataset Introduction
To explain the steps, we will use a dataset that contains information about the Department, Age and Working hour of some Employees. At first, we will apply filters to our dataset and then, count the filtered rows.
How to Count Filtered Rows in Excel with VBA: Step-by-Step Guideline
Here, we will discuss the steps to count filtered rows in excel with VBA. It is very easy to implement and the discussion will help you to understand the steps easily. Let’s pay attention to the steps below to learn the method.
STEP 1: Apply Filter in the Dataset
- In the first place, select any cell of your dataset. We have selected Cell B4 here.
- After that, go to the Data tab and select Filter. It will apply the filter to your dataset.
- After applying the filter, you will see results like below.
Read More: How to Count Rows with VBA in Excel
STEP 2: Launch the VBA Window to Count Filtered Rows in Excel
- Now, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- In the Visual Basic window, select Insert and select Module from the drop-down menu.
- After that, the Module window will appear.
Similar Readings:
- Excel VBA: Count Rows with Specific Data
- Excel VBA: Count Rows in a Sheet
- Excel VBA to Count Rows with Data
STEP 3: Type and Save the Excel VBA Code
- In this third step, type the below 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
Here, this code will count the filtered rows. You have to write the sheet number inside ThisWorkbook.Worksheets(). We are using Sheet 2. So, we have written ThisWorkbook.Worksheets(2) here. Again, you need to write the cell from where your dataset starts inside wks.range().CurrentRegion.Rows.Count. For this, we have written wks.range(“B4”).CurrentRegion.Rows.Count.
- Press Ctrl + S to save the code or click the Save icon.
- After that, close the Visual Basic window.
Read More: How to Count Rows with Data in Column Using VBA in Excel
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.
Read More: How to Count Rows in Selection Using VBA in Excel
Final Output after Running Excel VBA Code to Count Filtered Rows
- Finally, it will show results like below.
Here, the message box is showing the number of filtered rows including the header. As we have not applied any filter yet, it is displaying 7 in the message box.
- Now, apply the filter to the dataset. It will hide the rows that contain Accounting in the Department column.
- Again, go to the Developer tab and select Macros.
- Run the code from the Macro window.
- This time you will see results like below.
- To display the result without the header row, type the below 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
Here, we have used zCount = -1 instead of zCount = 0.
- Press Ctrl + S to save the code.
- In the end, press the F5 key to Run the code. It will display results like below.
Things to Remember
There are certain things you need to remember to count filtered rows in excel with VBA.
- First of all, you need to apply the filter before using VBA.
- Use zCount = -1 instead of zCount = 0 to count the rows number without the header.
- You can also run the code by pressing the F5 key after saving it.
Download Practice Book
Download the practice book.
Conclusion
We have demonstrated step-by-step guidelines to count filtered rows in excel. I hope this demonstration will help you to understand the method very easily. Furthermore, the practice book is also added at the beginning of the article. You can also download the practice book to exercise more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.
