Excel VBA: How to Check AutoFilter Status

In Excel VBA, check Worksheet.FilterMode to find out whether a worksheet currently has a filter applied, and check Worksheet.AutoFilterMode to find out whether its AutoFilter dropdown arrows are displayed. Those properties report different things: arrows can remain visible after filters are cleared, so use FilterMode when you mean “is this data filtered?”

What the AutoFilter status properties mean

Excel exposes two worksheet properties that are easy to confuse. AutoFilterMode is True when AutoFilter arrows are showing on the worksheet. It answers a display question: are the dropdown controls present? It does not, by itself, say whether any filter criteria are restricting the rows.

FilterMode is True when the worksheet is in filter mode because a filter has been applied. This is usually the property to test before deciding whether results are currently filtered. For example, after a user chooses a category in a column’s dropdown, FilterMode is normally True. If the user clears the criteria but leaves the dropdown arrows in place, FilterMode becomes False while AutoFilterMode can remain True.

These properties describe worksheet-level state. They are useful for broad checks, but they do not tell you which column has a criterion or what that criterion is. For those questions, inspect the AutoFilter’s individual Filters collection.

A quick VBA check for arrows and active filtering

This macro prints both states in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows, run the macro, and read the output there. Change the worksheet name to match your workbook.

Sub ReportAutoFilterStatus()
    Dim ws As Worksheet

    ' Change Data to the name of the worksheet to inspect.
    Set ws = ThisWorkbook.Worksheets("Data")

    If ws.AutoFilterMode Then
        Debug.Print "AutoFilter arrows are displayed."
    Else
        Debug.Print "AutoFilter arrows are not displayed."
    End If

    If ws.FilterMode Then
        Debug.Print "A filter is currently applied."
    Else
        Debug.Print "No filter is currently applied."
    End If
End Sub

Save a copy of the workbook before running any macro. VBA actions cannot be undone with Ctrl+Z, and a macro that changes data or filter settings may be difficult to reverse. This example only reads properties and writes messages to the Immediate window.

If you need a single yes-or-no result for a worksheet, use ws.FilterMode. If you are deciding whether to show or hide the dropdown controls, use ws.AutoFilterMode. Naming the variable or message after the exact state helps prevent later code from treating visible arrows as proof that rows are filtered.

Check whether one specific column is filtered

Each AutoFilter field has a corresponding entry in the Filters collection. Its On property is True when that field has a filter criterion. The field number is relative to the filtered range, not necessarily the worksheet column number. For example, field 1 means the first column in the AutoFilter range.

The following example checks field 2 on a worksheet-level AutoFilter and reports which fields are active. It assumes that the sheet has an AutoFilter range and that the range’s first row contains headers.

Sub ReportFilteredFields()
    Dim ws As Worksheet
    Dim af As AutoFilter
    Dim fieldIndex As Long

    ' Change Data to the worksheet to inspect.
    Set ws = ThisWorkbook.Worksheets("Data")

    If Not ws.AutoFilterMode Then
        Debug.Print "There is no worksheet AutoFilter to inspect."
        Exit Sub
    End If

    Set af = ws.AutoFilter

    For fieldIndex = 1 To af.Filters.Count
        If af.Filters(fieldIndex).On Then
            Debug.Print "Filtered field: " & fieldIndex
        End If
    Next fieldIndex
End Sub

To test only the second field, the central check is af.Filters(2).On, provided the AutoFilter range has at least two fields. A safer routine first checks that the AutoFilter exists and that the requested field number is within af.Filters.Count. Otherwise, an invalid field reference can cause a VBA runtime error.

Understand filter field numbers and range boundaries

A frequent source of incorrect results is assuming that a filter field number is an Excel column number. If the filtered range is C1:F100, its first field is worksheet column C, its second is D, and its fourth is F. In that example, Filters(2).On checks column D, not worksheet column B.

The AutoFilter range includes its header row. When you inspect a range or compare a field with a worksheet column, account for the range’s starting column. This matters especially when the filter covers only part of a sheet or when a macro is reused on sheets with differently positioned data.

Also distinguish a filtered field from a hidden row. A row may be hidden manually, hidden by an outline, or hidden for another reason. Conversely, an active criterion might match every record, so the presence of an applied criterion is not a dependable count of how many rows are visibly missing. Use Filter.On to ask whether that field has a criterion, and inspect visible rows separately if your task is to count or process displayed records.

Checking AutoFilter on an Excel table

Excel tables, also called ListObjects in VBA, have their own AutoFilter. If the data is a named table, address that table directly rather than assuming the worksheet-level AutoFilter object represents the filter you want. For a table named SalesTable on the Data sheet, you can inspect each table field like this:

Sub ReportTableFilterStatus()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim fieldIndex As Long

    ' Use the actual worksheet and table names in your workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    For fieldIndex = 1 To lo.AutoFilter.Filters.Count
        If lo.AutoFilter.Filters(fieldIndex).On Then
            Debug.Print "Table field filtered: " & fieldIndex
        End If
    Next fieldIndex
End Sub

Replace both names with the names shown in Excel. If the table name is wrong, VBA raises an error when it tries to retrieve the ListObject. Table field numbers are relative to the table’s columns, so field 1 is the table’s first column even if that column is far across the worksheet.

For a workbook that may contain either a table or a normal filtered range, decide which object owns the data before checking its state. A table’s filter controls are associated with that table; a worksheet may also contain other ranges or filters. Checking the intended object makes the result more dependable than relying on a broad worksheet test alone.

Why checking the actual criteria can be harder

The On property is generally the simplest way to establish whether an individual field is filtered. Reading the exact criterion requires more care. An AutoFilter can use a single value, a range condition, a date grouping, a multiple-selection list, or a dynamic condition. The criterion may therefore be represented in different ways, and some filter types use the Operator property as well as Criteria1 and, where relevant, Criteria2.

Do not assume that every filter’s Criteria1 is a plain string. For some multi-select filters it can be an array, and particular filter types may not expose a value in the form your code expects. A routine that reads criteria should first check that the field’s On property is True, then handle the filter type and possible errors deliberately. If your goal is only to decide whether a column is filtered, On avoids that unnecessary complexity.

Another common misconception is that AutoFilter status tells you the selected value. It does not: FilterMode is a broad worksheet state, while Filters(i).On tells you that a particular field is active. Neither alone gives a universal, easy-to-display description of every possible filter selection.

Common problems and reliable checks

Arrows are visible but no rows are filtered: This is expected when AutoFilterMode is True and FilterMode is False. Use the latter to test for an applied filter.
The field number checks the wrong column: Count from the first column of the AutoFilter range or table, not from worksheet column A.
The worksheet has no AutoFilter: Check AutoFilterMode before using ws.AutoFilter. For a table, retrieve the intended ListObject by name.
A macro errors while reading criteria: First check Filters(i).On. Criteria can have different data types or structures depending on how the filter was created.
Rows are hidden even though FilterMode is False: They may have been hidden manually or by another feature. Filter state is not the same as row visibility.

For code that should modify a filter, make the decision explicit. For example, if the goal is to clear an active worksheet filter, test FilterMode before calling a method that reveals all records. Do not call a clear operation merely because dropdown arrows exist: the arrows can be present when there is nothing to clear. Also avoid changing a user’s filter as a side effect of a macro whose purpose is only to report status.

Choosing the right check for your task

Use the narrowest check that answers your question. That keeps the macro’s intent clear and avoids surprising behavior:

Use Worksheet.AutoFilterMode to determine whether worksheet AutoFilter arrows are displayed.
Use Worksheet.FilterMode to determine whether the worksheet currently has filtering applied.
Use AutoFilter.Filters(fieldIndex).On to check one field’s filter state.
Use ListObject.AutoFilter.Filters(fieldIndex).On when checking a named Excel table.

When debugging, print the broad worksheet states first, then inspect the relevant filter object and field. This sequence helps identify whether the issue is that no filter exists, the wrong range is being checked, or the code is using the wrong field index. If the macro is intended to react to a particular column, checking that column’s On property is more informative than relying on worksheet-wide FilterMode.

Frequently Asked Questions

How do I know if an AutoFilter is on in VBA?
Use Worksheet.AutoFilterMode to check whether the AutoFilter dropdown arrows are displayed. Use Worksheet.FilterMode instead if “on” means a filter criterion is currently applied.

What is the difference between FilterMode and AutoFilterMode?
AutoFilterMode reports whether filter arrows are visible. FilterMode reports whether the worksheet is in a filtered state. The arrows can remain visible after the criteria have been cleared.

How do I check whether a particular column is filtered?
Use the field’s On property, such as ws.AutoFilter.Filters(2).On, after confirming the AutoFilter exists. The number is relative to the first column in the filtered range, not the worksheet’s column letters.

Does FilterMode tell me which filter value was selected?
No. It is a worksheet-level True-or-False status. Check the relevant entry in the AutoFilter’s Filters collection for its On state; interpreting the exact criteria requires additional handling because criteria can take different forms.

How do I check a filter on an Excel table?
Get the table as a ListObject, then check lo.AutoFilter.Filters(fieldIndex).On. Use the table’s actual name and count field numbers from its first column.

Why are rows hidden when FilterMode is False?
AutoFilter is not the only way to hide rows. Rows may be manually hidden or affected by other worksheet features, so a false filter status does not guarantee that every row is visible.

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.