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

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.


Download Practice Book

Download the practice book.


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.


Steps to Count Filtered Rows in Excel with VBA

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: How to Count Rows with VBA in Excel (5 Approaches)


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

Read More: Excel VBA to Count Rows with Data (4 Examples)


Similar Readings:


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.

Related Content: How to Count Rows with Data in Column Using VBA in Excel (9 Ways)


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

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.

Final Output after Running Excel VBA Code to Count Filtered Rows

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.

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.


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.

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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo