[Excel VBA] How to Get the Count of Filtered Rows Using the Subtotal Function

Overview

If you want to instantly get the number of records filtered by AutoFilter, calling WorksheetFunction.Subtotal from VBA is the easiest method.

By using the Subtotal function with function number 3 (COUNTA), you can automatically exclude hidden rows and count only the visible data. In this article, I will explain the key points with practical sample code.

Prerequisites

Sample Code (VBA)

Sub GetFilteredRecordCount()

    Dim recordCnt As Long          ' Stores the count of filtered records
    Dim tgtCol    As Range         ' The column to count
    
    ' Set the column to count (e.g., Column A)
    Set tgtCol = ActiveSheet.Columns(1)
    
    ' Specify function number 3 for COUNTA
    ' Subtract 1 to exclude the header row
    recordCnt = WorksheetFunction.Subtotal(3, tgtCol) - 1
    
    MsgBox "The number of currently filtered records is " & recordCnt & ".", vbInformation

End Sub

Code Explanation

• Set tgtCol = ActiveSheet.Columns(1): Sets the entire Column A as the target for the filter. Change the column number as needed.
• Subtotal(3, tgtCol): Counts only the visible cells (rows displayed after filtering).
• - 1: This subtracts the header row from the total count to get the actual data count.
• MsgBox: Displays the count to the user. This is useful for verification or for branching logic in subsequent processes.

Advanced Tips

• Dynamic Column Selection:
  • Search for the column number by header name and set tgtCol = Cells(1, matchCol).EntireColumn. This improves the versatility of your code.
• Confirmation Before Deletion:
  • Display the count of filtered records before running a deletion process. asking the user for confirmation can prevent accidental data loss.
• Writing to Sheet:
  • Write the result to a cell (e.g., Range("F2").Value = recordCnt) to visualize the count on a dashboard.

FAQ

Q: What function numbers are available for Subtotal?

A: Common examples include 1 (AVERAGE), 2 (COUNT), 3 (COUNTA), and 9 (SUM). Please refer to the official Microsoft reference for details.

Q: Will it count correctly if the column contains blank cells?

A: No, COUNTA excludes blank cells from the count. If you want to get the number of rows, select a column that does not contain any blank cells (such as an ID column).

Summary

By using WorksheetFunction.Subtotal, you can easily count only the visible cells after filtering.

First, run the sample code to confirm that the expected count is displayed. By adjusting the target column or how the result is used, you can apply this to a wide variety of workflows.