Excel VBA: Count Visible Rows After AutoFilter

To count rows still visible after an AutoFilter, loop through the filter’s data rows and count those whose worksheet rows are not hidden. In VBA, checking EntireRow.Hidden counts visible rows even when some cells in a record are blank; exclude the header row before calling the routine.

A reliable VBA method

Put this function in a standard VBA module. It accepts a contiguous range containing data rows only, checks each row’s hidden state, and returns the number that remain visible. AutoFilter hides entire worksheet rows, so the check reflects the filtered result without relying on a particular column being populated.

Function CountVisibleDataRows(ByVal dataRows As Range) As Long
    Dim oneRow As Range

    ' Return zero when the supplied range has no rows.
    If dataRows Is Nothing Then Exit Function

    ' Filtered-out and manually hidden rows are not visible.
    For Each oneRow In dataRows.Rows
        If Not oneRow.EntireRow.Hidden Then
            CountVisibleDataRows = CountVisibleDataRows + 1
        End If
    Next oneRow
End Function

For an Excel table named Table1 on a worksheet named Sales, call the function with the table’s DataBodyRange. That property contains the records, not the header or totals row. If the table has no data rows, its DataBodyRange is Nothing, so handle that before passing it to the function.

Sub ShowVisibleTableRows()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim visibleCount As Long

    Set ws = ThisWorkbook.Worksheets("Sales")
    Set lo = ws.ListObjects("Table1")

    ' An empty table has no DataBodyRange.
    If lo.DataBodyRange Is Nothing Then
        visibleCount = 0
    Else
        visibleCount = CountVisibleDataRows(lo.DataBodyRange)
    End If

    MsgBox visibleCount & " visible data rows"
End Sub

Change the worksheet and table names to match the workbook. Save a copy before running a macro: VBA changes cannot be undone with Ctrl+Z. The function only reads row visibility and displays a count; it does not change filters or worksheet data.

What “visible rows” means

A filtered list has a header and a set of data rows. The header remains visible while AutoFilter hides records that do not meet the selected criteria. The useful count is usually the number of visible data rows, excluding the header. The code above expects exactly that: pass a range that starts with the first record, not the field names.

There can be other hidden rows as well. For example, a person may manually hide a row, or a grouped outline may collapse it. The Hidden property is false only for rows that are not hidden, so this function excludes those rows too. That is appropriate if the question is “How many records can I see?” If you need to distinguish filter-hidden records from manually hidden records, record that state separately or clear manual hiding before counting; the row’s hidden property alone does not identify why it is hidden.

Counting rows in a worksheet AutoFilter range

For a regular range rather than a table, use the same function but pass only the rows beneath the filter headers. The worksheet’s AutoFilter.Range property represents the filtered range, including its header. A typical setup is to assign that range to a variable, then create a data range offset down one row and shortened by one row. Check that the filter range has more than one row before resizing it, because a header-only range has no data rows to count.

For example, if the AutoFilter range is A1:D50, it covers one header row and 49 possible records. The range supplied to CountVisibleDataRows should be A2:D50. If the filter is on a table, prefer the table’s DataBodyRange instead; it adjusts as table records are added or removed and does not require manually calculating the last row.

Make sure the worksheet variable refers to the sheet that owns the filter. A workbook can contain several sheets with filters, and using the wrong sheet’s range can produce a plausible but unrelated number. If a sheet has multiple independently filtered tables, use the relevant ListObject rather than assuming the worksheet-level AutoFilter range represents the table you mean.

When SUBTOTAL is a simpler choice

If every record has a nonblank value in a dependable key column, Excel’s SUBTOTAL function is a convenient alternative. It ignores rows excluded by a filter. Function number 103 means COUNTA while also excluding manually hidden rows, so it matches a visible-only count when the chosen column is filled in for every record.

With record IDs in A2:A7, all six IDs nonblank, and a filter leaving rows 2, 4, and 6 visible, enter =SUBTOTAL(103,A2:A7) in a cell outside the filtered range; the result is 3. If you want filtered-out rows ignored but manually hidden rows included, use function number 3 instead. Both variants exclude rows removed by AutoFilter.

The limitation is important: COUNTA counts nonempty cells, not records. If a visible record has a blank in the selected column, SUBTOTAL will not count it. Choose a column that is guaranteed to contain a value, such as a unique record ID, or use the VBA row-visibility loop when blanks are possible. A table’s data area may contain many columns, but a formula still needs a specific column to count.

Why SpecialCells can miscount

Another common VBA approach is to call SpecialCells(xlCellTypeVisible) and read the resulting range’s Rows.Count. That can be misleading: visible cells separated by filtered-out rows form multiple areas, and the Rows collection on a multi-area range does not necessarily represent the total rows across every area. Counting each area can work for a carefully chosen single-column range, but it needs additional care if hidden columns or other disjoint sections are involved.

SpecialCells can also raise a run-time error when there are no matching visible cells in the requested data range. A robust version must handle that case and account for every area. The row-by-row Hidden check avoids both the multi-area counting trap and the assumption that one column is populated. It is easier to audit, although on a very large worksheet a formula or a more specialized array-based approach may run faster.

Performance and changing filters

The function checks each row once, so its work increases in proportion to the number of records in the supplied range. For a typical filtered report, this is straightforward and reliable. Keep the range limited to the actual data instead of passing all one million worksheet rows; a needlessly large range wastes time and can make a macro appear stuck.

The function returns the count at the moment it runs. If a user changes a filter afterward, the value does not update automatically unless the macro runs again. Put the call behind a button, run it after applying the filter, or use a worksheet formula such as SUBTOTAL when the filled-key-column condition is satisfied and a live recalculating result is preferable. A VBA function placed in a cell is not automatically recalculated just because a filter selection changes in every setup, so do not assume a custom function will refresh as a filter-aware formula does.

Common problems to check

The header was counted: pass the data body only. For a table, use DataBodyRange; for a normal filter, omit the first header row.
The count is zero unexpectedly: verify that the supplied range is on the filtered worksheet and that the filter has not hidden every data row. Also check whether the range starts below the actual records.
Blank records are missing from a formula count: SUBTOTAL with COUNTA counts nonblank cells, not rows. Use a consistently filled key column or the VBA Hidden loop.
The number differs from what is on screen: check for manually hidden rows, collapsed groups, and whether the header or a totals row was included. The VBA function counts visible worksheet rows, regardless of why other rows are hidden.
The macro cannot find the table: confirm the exact table name in Table Design > Table Name and the worksheet name in the workbook. Table names are not the same thing as worksheet names.

Frequently Asked Questions

Does this count include the AutoFilter header?
No. The function counts the rows in the range you pass to it. Supply only data rows, such as a table’s DataBodyRange, to exclude the header.

Will it count a row if some cells in that row are blank?
Yes. It tests whether the worksheet row is hidden, not whether its cells contain values. A visible row is counted even if one or more fields are empty.

Does it count manually hidden rows?
No. A manually hidden row also has Hidden set to True, so the function excludes it. This is useful for a visible-row total but means the result is not limited to rows hidden specifically by AutoFilter.

Can I use this with an Excel table?
Yes. Pass the table’s DataBodyRange to the function. It excludes the header and adapts to the table’s current number of records; check for Nothing first when the table is empty.

Why not just use SUBTOTAL?
SUBTOTAL is simpler when a selected column is filled in for every record. A VBA visibility check is better when records may have blanks in that column or when the count must be based on visible rows themselves rather than nonempty cells.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.