Excel VBA: Check If AutoFilter Is Active on a Sheet

In Excel VBA, check Worksheet.AutoFilterMode to find out whether AutoFilter arrows are displayed, and check Worksheet.FilterMode to find out whether the worksheet currently has a filter applied. These properties answer different questions: arrows can be visible when no rows are filtered, so choose the property that matches what you mean by “active.”

The Two Meanings of “AutoFilter Is Active”

Excel’s user interface can make “active” sound like one state, but VBA separates the presence of AutoFilter controls from the act of filtering data. The distinction is the key to avoiding a misleading test.

• Worksheet.AutoFilterMode is True when AutoFilter dropdown arrows are displayed on the worksheet. It is False when those arrows are not displayed.
• Worksheet.FilterMode is True when the worksheet is currently in a filtered state. It is False when it is not currently filtered.

For example, a list can have its dropdown arrows turned on while every item remains visible. In that situation, AutoFilterMode is true and FilterMode is false. If a filter criterion hides some records, both will usually be true. If the arrows are removed, AutoFilterMode is false; do not use it as a substitute for checking whether a particular table or range has filter criteria.

Check a Worksheet with VBA

This macro reports both states for a worksheet named Data. Replace that name with the worksheet you want to inspect. It uses ThisWorkbook, meaning the workbook containing the macro; if the code is stored in another workbook, explicitly refer to the workbook that contains the data instead.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even when a particular example only reads worksheet properties.

Sub CheckAutoFilterState()
    ' Check whether the Data worksheet has filter arrows or active filtering.
    Dim ws As Worksheet
    Dim arrowsVisible As Boolean
    Dim rowsFiltered As Boolean

    Set ws = ThisWorkbook.Worksheets("Data")
    arrowsVisible = ws.AutoFilterMode
    rowsFiltered = ws.FilterMode

    Debug.Print "AutoFilter arrows visible: " & arrowsVisible
    Debug.Print "Worksheet is filtered: " & rowsFiltered
End Sub

Run the macro from the Visual Basic Editor, then open the Immediate Window to see the two True or False results. In the Windows Visual Basic Editor, Ctrl+G opens that window; on Mac, use the editor’s View menu and choose Immediate Window if the shortcut differs. Excel for the web does not run VBA macros. The VBA editor and available shortcuts can differ between Windows and Mac, even when the worksheet properties used by the code are the same.

Interpret the Results Correctly

Use the result that matches the task you are automating. If you are deciding whether to tell a user that dropdown controls are present, test AutoFilterMode. If you need to know whether the worksheet is currently filtered, test FilterMode. If your code needs both pieces of information, read both properties rather than trying to infer one from the other.

A simple branching example is:

If Not ws.AutoFilterMode Then
    Debug.Print "No AutoFilter arrows are displayed."
ElseIf ws.FilterMode Then
    Debug.Print "AutoFilter arrows are displayed and the sheet is filtered."
Else
    Debug.Print "AutoFilter arrows are displayed, but the sheet is not filtered."
End If

This fragment assumes ws has already been set to a valid Worksheet object. It distinguishes three useful situations, but it does not identify which column has a criterion or count the hidden records. For those tasks, inspect the relevant filter or data range rather than treating the two worksheet-wide Boolean properties as a detailed filter report.

Check a Table Separately

Excel tables, represented in VBA by ListObject objects, have their own filter controls. If the question is about a named table, refer to that table directly instead of assuming that a worksheet-level test describes every detail of its filter. A table can show its dropdowns without having any column filtered. The table’s ShowAutoFilter property reports whether its filter buttons are shown; each item in its AutoFilter.Filters collection has an On property indicating whether that field has a filter applied.

This example checks a table named SalesTable on the Data worksheet and prints whether any table field has a filter switched on:

Sub CheckTableFilterState()
    ' Check the filter buttons and criteria for one Excel table.
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim i As Long
    Dim anyFieldFiltered As Boolean

    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    Debug.Print "Table filter buttons shown: " & lo.ShowAutoFilter
    For i = 1 To lo.AutoFilter.Filters.Count
        If lo.AutoFilter.Filters(i).On Then
            anyFieldFiltered = True
            Exit For
        End If
    Next i
    Debug.Print "At least one table field filtered: " & anyFieldFiltered
End Sub

Change both worksheet and table names to match the workbook. If the named table does not exist, VBA will raise an error when it tries to retrieve it, so code that handles variable or optional tables should first verify that the table is present. The loop checks for an active criterion on any table field; it does not reveal the criterion’s value or whether it is a text, date, number, color, or multi-select filter.

Choose the Right Test for Common Tasks

Before writing the condition, pin down what your next step depends on. These are common interpretations:

• Show a message when dropdown arrows are absent: test Not ws.AutoFilterMode.
• Run cleanup only when the worksheet is filtered: test ws.FilterMode.
• Tell whether table buttons are visible: inspect the specific table’s ShowAutoFilter property.
• Tell whether any table column has an active criterion: inspect each table filter’s On property.
• Find the filtered range: when worksheet AutoFilter arrows are present, inspect ws.AutoFilter.Range. Check that AutoFilter is present before referring to its range.

The last point matters when a worksheet contains more than one data area or a mixture of ordinary ranges and tables. A worksheet-wide Boolean is a useful state check, not a universal way to identify which object your code should modify. For reliable automation, keep a reference to the intended Range or ListObject and check that object’s state.

Common Problems and Misconceptions

“AutoFilterMode is true, so some rows must be hidden.” Not necessarily. It reports the presence of AutoFilter arrows, not whether a criterion has hidden records. Use FilterMode for the worksheet’s filtered state, or inspect the filter fields on a specific table.

“FilterMode is false, so no rows are hidden.” That conclusion is also too broad. Rows can be hidden manually, or by other workbook features, without the worksheet being in AutoFilter filter mode. These properties report AutoFilter-related state; they are not general tests for whether every row is visible.

“A worksheet has one filter state, so that identifies my table.” A worksheet-level property does not name the range or table that your procedure intends to work with. In workbooks with multiple lists, use the relevant table name or range reference and avoid applying an operation to whichever filter happens to be on the active sheet.

“The active sheet is always a worksheet.” VBA’s ActiveSheet can refer to a chart sheet as well as a worksheet. Assigning it directly to a Worksheet variable can fail if a chart sheet is active. A named reference such as ThisWorkbook.Worksheets("Data") is clearer when the target is known. If a macro must work with the active tab, first confirm that it is a worksheet.

“Ctrl+Z will reverse a macro.” Excel does not provide the normal Undo history for VBA macros. Back up the workbook before running code that changes filters, rows, or data. The examples above only inspect properties, but the same precaution is important when adapting them to make changes.

Showing All Records Without Removing the Arrows

A common follow-up is how to clear the current filter while leaving the dropdown buttons available. On a worksheet in AutoFilter filter mode, ShowAllData displays all records again. Check FilterMode before calling it, because calling ShowAllData when no data is filtered can raise an error.

Sub ClearWorksheetFilter()
    ' Show all records only when the worksheet is currently filtered.
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

This clears the active filtering rather than turning off the AutoFilter arrows. That is different from removing the AutoFilter controls with AutoFilterMode = False. Be deliberate about which outcome you want: users often expect the buttons to remain available after a macro clears criteria. For a table-specific operation, target the table and verify its state rather than applying a worksheet-wide action without checking which list is involved.

Alternatives When a Boolean Is Not Enough

Sometimes “is it active?” is only the first question. If you need to know which column is filtered, examine the filter collection for the particular AutoFilter object and check each field’s On property. If you need the criterion itself, the available details depend on the filter type; date groups, multiple selections, and color filters do not all reduce to a single simple value. Write and test that logic against the kinds of filters your workbook actually uses.

If the goal is to count visible records rather than inspect VBA state, a worksheet formula or a visible-cells operation may be more appropriate. These answer a different question: a visible-row count can be affected by manually hidden rows and other conditions, whereas FilterMode reports filter mode. Choose an approach based on whether you care about the controls, the criteria, or the resulting visible data.

Frequently Asked Questions

Does AutoFilterMode mean a filter criterion is applied?
No. It means AutoFilter dropdown arrows are displayed. Check FilterMode for the worksheet’s filtered state, or inspect a table’s filter fields when you need a table-specific answer.

How do I check whether rows are filtered in VBA?
For a worksheet, test If ws.FilterMode Then. This reports AutoFilter filter mode; it does not detect every reason a row might be hidden.

Can AutoFilter arrows be visible when FilterMode is false?
Yes. A list can have its AutoFilter controls enabled and show all its records. In that case, the arrows are visible but the worksheet is not currently filtered.

Does this VBA work in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run VBA, noting that the editor and some shortcuts differ by platform.

How can I remove the filter but keep the dropdown arrows?
When the worksheet is filtered, call ws.ShowAllData after checking ws.FilterMode. This displays all records without turning off the AutoFilter controls.

Related

• Excel Page Break Character: Meaning and Print Breaks
• Excel Page Break Dashed Line: What It Means
• Excel Power Query Editor: How to Open It

More in VBA & macros

• Excel VBA New Line: Add Line Breaks in Cells and TextIn Excel VBA, join text with vbCrLf or vbNewLine to put it on separate lines in a message…
• Excel Sheet Open VBA: Open and Activate a WorksheetTo open a worksheet that is already in an Excel workbook, use VBA to activate it: ThisWorkbook.Worksheets("Summary").Activate. If…
• Excel VBA Copy and Paste Range: Examples and TipsIn Excel VBA, copy a range to another location with sourceRange.Copy Destination:=destinationRange. For values only, assign the ranges…
• Excel VBA Pivot Table Source Types ExplainedIn Excel VBA, the main PivotTable source types are xlDatabase for a worksheet range or table, xlExternal for…
• Excel VBA: Copy a Worksheet to Another Workbook SafelyTo copy a worksheet from one open workbook to another in Excel VBA, use the worksheet’s Copy method…
• How to Copy and Paste Using VBA in Excel: Macro GuideTo copy a range in Excel with VBA, use Range.Copy Destination:=...; for values only, assign one range’s .Value…

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

Spotted an error in this guide? Let us know.