To count data rows visible after an Excel AutoFilter, count the visible cells in one column of the filter’s data range, excluding its header. The VBA example below does that even when the chosen column contains blank cells; it counts rows visible on screen, not the number of filter criteria.
What “AutoFilter count” can mean
People use “VBA AutoFilter count” to describe several different tasks. The most common is counting records that remain visible after a filter is applied. But someone may instead want the count of all data rows, the number of active filter fields, or the visible count in a particular column. Decide which result you need before choosing code, because these counts are not interchangeable.
For example, imagine a list with 100 records. A filter on the Region field might leave 18 rows on screen. The visible-row count is 18, while the full list still has 100 records. If filters are set on both Region and Status, the number of active filter fields is two, even if the combined criteria leave only three records visible.
- Visible rows: records currently displayed after filtering, excluding the header.
- Nonblank visible cells: visible cells with a value in a particular column; this can be less than the visible-row count.
- Active filter fields: columns whose filter criteria are switched on.
- Total records: all records in the list, including rows hidden by the filter.
In most reporting tasks, “count the AutoFilter” means count visible records. The rest of this article distinguishes that from the other interpretations so a macro returns the number the user expects.
How AutoFilter affects row visibility
Excel’s AutoFilter hides rows that do not meet the selected criteria. It does not delete those records or remove them from the worksheet. In VBA, a filtered-out row is hidden, so code can inspect visible cells or row visibility to determine what remains displayed.
A standard worksheet AutoFilter has a header row and a data range. A useful count must exclude the header; otherwise, a list with 18 matching records could be reported as 19. It should also avoid relying on a data column that may be blank, unless the task specifically asks for the number of nonblank values in that field.
One subtlety is that Excel can also hide rows manually. A count based on visible cells will exclude both rows hidden by the filter and rows hidden manually. That usually matches what a person means by “what can I see?” But if the count must distinguish AutoFilter-hidden rows from manually hidden rows, a simple visible-cell count is not enough; the workflow needs to control or account for manual hiding separately.
VBA code to count visible data rows
This macro assumes a standard worksheet AutoFilter on a worksheet named Data. It looks at the first column within the filter range, but counts its visible cells whether or not those cells contain values. Because it counts visible row areas rather than nonblank values, blank cells in that column do not lower the result.
Sub CountVisibleAutoFilterRows()
Dim ws As Worksheet
Dim filterRange As Range
Dim dataRows As Range
Dim visibleCells As Range
Dim area As Range
Dim visibleCount As Long
' Change Data to the worksheet that contains the AutoFilter.
Set ws = ThisWorkbook.Worksheets("Data")
' The worksheet must have AutoFilter arrows and a filter range.
If Not ws.AutoFilterMode Then
MsgBox "No worksheet AutoFilter is set up."
Exit Sub
End If
Set filterRange = ws.AutoFilter.Range
' A range with just a header has no data rows to count.
If filterRange.Rows.Count < 2 Then
MsgBox "There are no data rows to count."
Exit Sub
End If
' Exclude the header and inspect one column of the data range.
Set dataRows = filterRange.Offset(1, 0).Resize(filterRange.Rows.Count - 1, 1)
' SpecialCells raises an error when no data cells are visible.
On Error Resume Next
Set visibleCells = dataRows.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not visibleCells Is Nothing Then
For Each area In visibleCells.Areas
visibleCount = visibleCount + area.Rows.Count
Next area
End If
MsgBox "Visible data rows: " & visibleCount
End Sub
The code adds the row count of each visible area in the chosen column. For instance, if the filter leaves 18 records visible, the message reports Visible data rows: 18, not 19, because the header is outside dataRows. If no data rows pass the filter, the message reports zero.
Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. Save a macro-enabled workbook as an .xlsm file if you need to keep the macro. On Windows, open the Visual Basic Editor with Alt+F11; on a Mac, the shortcut may depend on the keyboard’s function-key settings, or you can use Excel’s Tools > Macro > Visual Basic Editor menu. Excel for the web does not run VBA macros.
Why counting visible cells is safer than counting values
A quick approach is to count nonblank cells in a column after filtering. That works only when every record has a value in that column. If some visible records have a blank ID, name, or other chosen field, a nonblank-cell count undercounts the rows. Conversely, if the column contains repeated values, counting unique values would answer yet another question.
The example uses SpecialCells(xlCellTypeVisible) on a one-column range and totals the row counts of its visible areas. The one-column choice matters: if code checks the entire multi-column range and totals area row counts, the same visible record might be counted more than once because separate areas can represent different columns as well as different row blocks.
There is an edge case worth knowing: if no data rows are visible, SpecialCells can raise an error because there are no matching cells. The macro handles that case with a narrowly scoped On Error Resume Next, then restores normal error handling immediately. It does not treat the absence of visible cells as a fatal problem; the count remains zero.
Using SUBTOTAL when a key column is complete
If every record has a value in a particular key column, Excel’s SUBTOTAL function can be a compact alternative. In a worksheet with headers in row 1, record IDs in A2:A101, and a filter applied to that list, enter =SUBTOTAL(103,A2:A101) in a cell outside the filtered list. If 18 visible records have nonblank IDs, the result is 18.
The 103 function number tells SUBTOTAL to count nonblank cells while excluding rows hidden by filters and rows hidden manually. Function number 3 also counts nonblank cells while ignoring filtered-out rows, but it includes manually hidden rows. Choose between them based on whether manually hidden records should count. Both versions count values, not rows in the abstract, so a blank key cell will not contribute.
VBA can calculate the same kind of result with WorksheetFunction.Subtotal, but it inherits the same nonblank-column limitation. The visible-cell-area method is more suitable when a row should count even if the inspected field is empty. For a quick worksheet total with a guaranteed-populated ID column, however, SUBTOTAL is often easier to inspect and maintain.
Counting active filter fields instead of records
Sometimes the requested “count” is the number of columns currently filtered, not the number of visible rows. A worksheet AutoFilter exposes its fields through the Filters collection. The following macro counts fields whose On property is true; it does not count matching records.
Sub CountActiveAutoFilterFields()
Dim ws As Worksheet
Dim filterField As Filter
Dim activeCount As Long
Set ws = ThisWorkbook.Worksheets("Data")
If Not ws.AutoFilterMode Then
MsgBox "No worksheet AutoFilter is set up."
Exit Sub
End If
For Each filterField In ws.AutoFilter.Filters
If filterField.On Then
activeCount = activeCount + 1
End If
Next filterField
MsgBox "Active filter fields: " & activeCount
End Sub
A field is active when a filter has been applied to that column. Thus, filtering Region to “West” and Status to “Open” gives an active-field count of two. Those criteria might leave 18 rows, one row, or no rows, so this macro should not be used as a substitute for the visible-row count.
Common problems and how to avoid them
- The header is included: start the count range one row below the AutoFilter header, as the main macro does.
- Blank values cause an undercount: count visible row areas, or use
SUBTOTALonly on a field guaranteed to be populated. - No matching rows cause an error: handle the no-visible-cells case around
SpecialCells. - The wrong sheet is checked: qualify the worksheet explicitly rather than relying on whichever sheet happens to be active.
- A table is being mistaken for a worksheet AutoFilter: Excel tables have their own AutoFilter behavior. If a macro targets a table, use that table’s range and confirm the code is addressing the intended table rather than assuming
ws.AutoFilter.Rangerepresents it. - Filters are cleared but arrows remain: the presence of AutoFilter arrows does not necessarily mean criteria are active. The visible-row macro will then count all visible data rows; the active-field macro will return zero if no fields have criteria switched on.
For a workbook with multiple filtered lists, avoid depending on an unspecified active range. Identify the correct worksheet and, for tables, the specific table. Test the result with a known filter that leaves a small, easy-to-count set of records, as well as with a filter that leaves none.
Choosing the right counting method
Use the visible-cell method when the question is “How many records can the user currently see?” and a row should count even when the inspected column is blank. Use SUBTOTAL(103,...) when you need a short worksheet formula and have a reliably nonblank key field. Use SUBTOTAL(3,...) if manually hidden rows should remain part of the count. Use the Filters collection only when the desired answer is how many filter fields have active criteria.
Also decide whether manually hidden rows belong in the result. “Visible rows” naturally excludes them, while “rows matching the AutoFilter criteria” may be intended to include them. Excel’s display can combine both kinds of hiding, so make that requirement explicit when a report must reconcile to another total.
Frequently Asked Questions
How do I count filtered rows in VBA without counting the header?
Use the AutoFilter range’s data rows, starting one row below its header, and count only visible rows. The first macro does this by applying SpecialCells(xlCellTypeVisible) to the data range and summing visible areas.
Does SUBTOTAL count rows hidden by AutoFilter?
No. The relevant SUBTOTAL count options ignore rows filtered out by AutoFilter. They count nonblank cells in the selected column, however, so blank cells can make the result smaller than the number of visible records.
What is the difference between 3 and 103 in SUBTOTAL?
Both count nonblank cells and ignore rows hidden by a filter. Function number 3 includes manually hidden rows, while 103 excludes them as well. Neither option counts blank cells as records.
How can I count how many AutoFilter criteria are active?
Loop through Worksheet.AutoFilter.Filters and increment a counter for each filter whose On property is true. That counts filtered columns, not the records that pass their criteria.
Will this VBA macro work in Excel for the web?
No. Excel for the web can display workbooks containing macros, but it does not run VBA. Run the macro in the Excel desktop application; the Windows and Mac desktop apps support VBA, though some shortcuts and interface details differ.
Related
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
- Power Query Split Cell Into Rows: Steps and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Editor in Excel: Where to Find It and How to OpenIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor (VBE), where you…
- Excel Macro to Merge Cells: VBA Steps and LimitsYes. In Excel for Microsoft 365 on Windows, a VBA macro can merge the currently selected cells with…
- VBA Find the Maximum Value in an Excel Column: How-ToTo find the largest numeric value in an Excel column with VBA, use the worksheet function Max on…
- Excel Macro Language: VBA, Office Scripts, and MoreExcel’s traditional macro language is Visual Basic for Applications (VBA), which automates tasks in the desktop app. Microsoft…
- VBA PivotTable Range: Source, Output, and ExamplesIn VBA, a PivotTable’s source range is the worksheet data used to build or refresh it, while its…
- Excel VBA VLOOKUP: Examples, Errors, and AlternativesIn Excel VBA, use Application.VLookup(lookup_value, table_array, column_index, False) to find an exact match in the first column of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.