VBA AutoFilter Mode: How to Check and Control It

In Excel VBA, Worksheet.AutoFilterMode tells you whether AutoFilter dropdown arrows are displayed on a worksheet; it does not tell you whether any rows are currently hidden by filter criteria. Use Worksheet.FilterMode for that second question. Checking both properties helps you decide whether to clear filter criteria, remove the arrows, or apply a filter.

What AutoFilter mode means

“AutoFilter mode” usually refers to the worksheet’s AutoFilterMode property. It is a Boolean value: True means AutoFilter arrows are displayed, and False means they are not. In the Excel interface, these are the dropdown buttons in the header row of a filtered range or table.

The property is read/write, but setting it to False turns off the worksheet’s AutoFilter arrows; setting it to True is not the way to create a filter. To add arrows, apply AutoFilter to a range with the Range.AutoFilter method, or use the table’s filter controls. This distinction is a common source of VBA errors: a worksheet can have arrows but no active criteria, or it can be filtered so that some records are hidden.

AutoFilterMode versus FilterMode

Excel provides two related worksheet properties, and they answer different questions. AutoFilterMode describes the controls, while FilterMode describes the current filtering state. Neither property tells you which exact criteria a user selected.

• ws.AutoFilterMode is True when AutoFilter dropdown arrows are showing.
• ws.FilterMode is True when the worksheet is currently displaying a filtered subset of its data.

For example, a list may have arrows visible but show every record. In that case, AutoFilterMode is true and FilterMode is false. If a user filters the list to show only the West region, both can be true. After clearing the criteria, the arrows can remain visible while FilterMode becomes false. If the arrows have been removed, AutoFilterMode is false.

These properties are worksheet-level indicators. They are useful for deciding what action to take, but they are not a complete description of every table’s individual filter settings. If a worksheet contains multiple tables or filterable ranges, inspect the relevant range or ListObject as well when your code needs table-specific behavior.

How to check the current state in VBA

Set a worksheet variable explicitly so the code checks the intended sheet, rather than whichever sheet happens to be active. The following example writes both states to the Immediate window in the Visual Basic Editor. If the sheet name differs in your workbook, change Data to the exact tab name.

Sub CheckFilterState()
    Dim ws As Worksheet

    ' Check the intended worksheet.
    Set ws = ThisWorkbook.Worksheets("Data")

    Debug.Print "Arrows visible: " & CStr(ws.AutoFilterMode)
    Debug.Print "Rows filtered: " & CStr(ws.FilterMode)
End Sub

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert or select a standard module, and run the procedure. The Immediate window is available from the editor’s View menu. On a Mac, the Visual Basic Editor is available through the Developer tab; the keyboard shortcut may depend on the keyboard and function-key settings. Excel for the web does not run VBA macros.

Before running a macro that changes a workbook, save a copy. VBA actions cannot be reversed with Ctrl+Z, so a backup is the safest way to recover from an unexpected result.

Clear filter criteria but keep the arrows

To show all rows again while leaving the dropdown controls in place, use ShowAllData only when the worksheet is filtered. Calling it without checking can cause a run-time error when there is no active filter to clear.

Sub ClearFilterCriteria()
    Dim ws As Worksheet

    ' Use the worksheet that contains the filtered list.
    Set ws = ThisWorkbook.Worksheets("Data")

    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

This code restores the visible records; it does not remove the AutoFilter arrows. That is often the desired behavior when a macro should reset a report but leave its controls available for the next user. The check is important because a list can have AutoFilter arrows on display even when no criteria are active.

Remove the AutoFilter arrows

If the goal is to turn off AutoFilter for an ordinary filtered range, set AutoFilterMode to False. Check the property first so the macro only changes the sheet when arrows are present.

Sub RemoveAutoFilterArrows()
    Dim ws As Worksheet

    ' Target the worksheet that owns the range filter.
    Set ws = ThisWorkbook.Worksheets("Data")

    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
End Sub

Removing arrows and clearing criteria are not interchangeable operations. Use ShowAllData to reveal all records while retaining the controls. Use AutoFilterMode = False when you want to remove the controls from a range. If you are working with an Excel table, prefer table-aware code and inspect that table’s AutoFilter or ShowAutoFilter settings rather than assuming a worksheet-level operation is the right way to manage every table.

Apply a filter with VBA

To display AutoFilter arrows and filter a range, call AutoFilter on the range that includes the header row. In the example below, the headers are in row 1, the data occupies columns A through C, and column B contains region names. The field number is relative to the selected range: field 2 means the second column of A:C, which is column B on the worksheet.

Sub FilterForWestRegion()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Include the header row in the filter range.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:C101")

    dataRange.AutoFilter Field:=2, Criteria1:="West"
End Sub

This applies the criterion to the second field and displays matching records. Adjust the range to match the actual list, including its header row; a hard-coded range that omits new records will not automatically include them. For a changing dataset, developers commonly determine the last used row or use an Excel table, whose structured range expands as rows are added.

Calling AutoFilter with criteria is different from merely setting AutoFilterMode. The method both applies filtering and uses the range’s header row for the dropdowns. The field number is not an absolute worksheet column number, so a range beginning in column D would use field 1 for column D.

Common problems and reliable patterns

A frequent mistake is testing only AutoFilterMode and assuming that the data is filtered. That test can be true when every row is visible. Use FilterMode when your next action depends on whether criteria are hiding records, and use AutoFilterMode when your question is whether the arrows are displayed.

• Run-time error when clearing: Check FilterMode before calling ShowAllData. A visible dropdown alone does not mean there is anything to clear.
• Wrong column filtered: Count fields from the left edge of the range passed to AutoFilter, not from worksheet column A.
• New rows are not included: Update the range or use a table so the filter covers the expanded data.
• Macro affects the wrong sheet: Qualify ranges with a worksheet variable instead of relying on ActiveSheet or an unqualified range.
• Arrows disappear unexpectedly: Check whether code sets AutoFilterMode to false. That removes the controls rather than simply clearing the current criteria.

Also remember that a hidden row does not necessarily mean AutoFilter is active. A user or macro can hide rows manually, and outlines can collapse rows too. FilterMode reports the worksheet’s filter state; it is not a general test for every reason a row might be invisible.

Working with tables and other Excel versions

An Excel table, created with Insert > Table, has its own header dropdowns and can expand as data is added. Table-aware VBA is generally a better fit when the workbook’s data is organized as a ListObject. For example, a macro can refer to a named table through ws.ListObjects("SalesTable") and operate on its range rather than assuming that a fixed worksheet range always represents the full dataset.

When a sheet contains both an ordinary AutoFilter range and one or more tables, do not treat a single worksheet Boolean as a complete inventory of each object’s filter criteria. Identify which list the macro is meant to control and work with that range or table. This makes the code easier to maintain and helps avoid removing controls that belong to a different list.

The VBA concepts described here apply to desktop Excel versions that support VBA, including Microsoft 365 for Windows and Mac, though interface steps and keyboard shortcuts differ. Excel for the web can display workbooks containing macros but cannot run VBA. A workbook must be saved in a macro-enabled format, such as .xlsm, if its VBA code needs to be retained.

Frequently Asked Questions

What does AutoFilterMode = True mean in VBA?
It means AutoFilter dropdown arrows are displayed on the worksheet. It does not by itself mean that any records are hidden by criteria.

How can I tell whether a worksheet is actually filtered?
Check Worksheet.FilterMode. When it is true, the worksheet is in a filtered state; use AutoFilterMode separately to check whether the dropdown arrows are showing.

How do I show all rows without removing the filter arrows?
If ws.FilterMode is true, call ws.ShowAllData. This clears the active criteria while leaving the AutoFilter controls available.

Can I turn on AutoFilter by setting AutoFilterMode to True?
Do not use that property to create the filter. Apply AutoFilter to the intended range with Range.AutoFilter, including the header row.

Why does ShowAllData give an error?
It commonly happens when no active filtering is in effect. Test FilterMode first, and call ShowAllData only when the worksheet is filtered.

Can Excel for the web run this VBA code?
No. Excel for the web does not run VBA macros. Use desktop Excel for VBA, or consider Office Scripts for suitable browser-based automation.

Related

• Multi-Level Dropdown List in Excel: How to Create One
• Excel Insert Page Breaks: Steps, Options, and Fixes
• Excel Box and Whisker Plot: How to Create One

More in VBA & macros

• VBA Loop Structures: How to Choose and Use Each TypeVBA has three main loop structures: For...Next for a known number of repetitions, For Each...Next for processing items…
• Excel VBA: Delete Rows Based on a Cell Value MatchTo delete worksheet rows when a cell matches a value, loop upward from the last used row and…
• Fortify Macro Recorder: Does Fortify Record Macros?No: OpenText Fortify is an application-security product family, not a macro recorder, and its standard tools do not…
• Access VBA Function Examples for Forms, Queries, and TablesAccess VBA functions are reusable procedures that return a value, such as a formatted name, a calculated amount,…
• VBA Range Is Nothing? Meaning, Checks, and Common FixesIn Excel VBA, a Range is Nothing when an object variable does not currently refer to a range…
• VBA Copy and Paste Values Only in ExcelTo copy a range in Excel VBA and paste only its values, use Copy followed by PasteSpecial Paste:=xlPasteValues.…

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

Spotted an error in this guide? Let us know.