How to Count Filtered Rows in Excel with VBA (Step-by-Step Guideline)

Get FREE Advanced Excel Exercises with Solutions!

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.

Apply Filter in the Dataset

  • After that, go to the Data tab and select Filter. It will apply the filter to your dataset.

Apply Filter in the Dataset

  • After applying the filter, you will see results like below.

Apply Filter in the Dataset

Read More: Excel VBA to Count Rows with Data


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.

Launch the VBA Window to Count Filtered Rows in Excel

  • In the Visual Basic window, select Insert and select Module from the drop-down menu.

Launch the VBA Window to Count Filtered Rows in Excel

  • After that, the Module window will appear.

Launch the VBA Window to Count Filtered Rows in Excel


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

Type and Save the Excel VBA Code

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.

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.

Run the VBA Code to Count Filtered Rows in Excel

  • Select the code and click Run from the Macro window.

Run the VBA Code to Count Filtered Rows in Excel


Final Output after Running Excel VBA Code to Count Filtered Rows

  • Finally, it will show results like below.

Final Output after Running Excel VBA Code to Count Filtered Rows

Here, the message box shows 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.

Final Output after Running Excel VBA Code to Count Filtered Rows

  • 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.

Read More: Excel VBA: Count Rows with Specific Data


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 row numbers 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo