VBA Remove AutoFilter: Clear Filters or Remove Arrows

VBA & macros · Updated November 18, 2026

To clear the active filters on a worksheet but keep its dropdown arrows, use If ws.FilterMode Then ws.ShowAllData. To remove a worksheet AutoFilter and its arrows, clear any filtered rows first and then set ws.AutoFilterMode = False; Excel tables need separate handling because their filter arrows are controlled by the table.

What “remove AutoFilter” can mean

In Excel, people use “remove AutoFilter” to describe two different actions. The first is clearing the current filter criteria so every row is visible again, while leaving the dropdown arrows in place for future filtering. The second is turning off the filter controls themselves, removing the arrows from the header row. Choosing the wrong action can make a macro behave differently from what a user expects.

Excel exposes these states separately. A worksheet’s FilterMode property indicates whether data on that sheet is currently filtered. Its AutoFilterMode property indicates whether AutoFilter arrows are displayed. The properties are independent: arrows can be visible when no rows are filtered, and a filtered view can be active while the arrows are still available.

The examples below target Excel for Microsoft 365 on Windows and use the active worksheet unless stated otherwise. If your data is formatted as an Excel table, see the table section: a table has its own setting for showing filter buttons.

Clear filters and keep the arrows

Use ShowAllData when the goal is to display every row without removing the filter controls. This is usually the right choice before a macro reads, copies, or summarizes a complete data list. The conditional check matters: calling ShowAllData when the sheet is not filtered can raise a run-time error.

Sub ClearFiltersKeepArrows()
    Dim ws As Worksheet

    Set ws = ActiveSheet

    ' Show every row only when a filter is active.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

This clears active filtering on the worksheet and leaves its AutoFilter controls available. It does not delete rows, change the data, or remove the filter range. If the sheet has multiple filtered lists, treat this as a worksheet-level operation rather than assuming it affects only one particular header.

Remove worksheet AutoFilter arrows

To remove a standard worksheet AutoFilter, first show all rows if a filter is active, then turn off the worksheet’s AutoFilter arrows. The order makes the intention clear: clear the filtered view, then remove the filter interface.

Sub RemoveWorksheetAutoFilter()
    Dim ws As Worksheet

    Set ws = ActiveSheet

    ' Clear active criteria before removing the controls.
    If ws.FilterMode Then
        ws.ShowAllData
    End If

    ' Remove standard worksheet AutoFilter arrows.
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
End Sub

Run this only on a sheet where removing the standard AutoFilter is appropriate. ActiveSheet means whichever sheet is selected when the macro runs; it is convenient for a quick task but can be risky in a larger procedure. To target a specific sheet in the same workbook, replace the assignment with Set ws = ThisWorkbook.Worksheets(1), or use the sheet’s tab name in quotes. Confirm the target before running the macro.

Setting AutoFilterMode to False is the VBA operation for removing worksheet AutoFilter arrows. Do not confuse it with FilterMode, which describes whether filtering is currently hiding rows. A check such as If ws.FilterMode Then is not a substitute for turning off the arrows.

Handle Excel tables separately

A formatted Excel table, also called a ListObject in VBA, has a table-level property named ShowAutoFilter. Setting it to False hides that table’s filter buttons; setting it to True displays them again. This is distinct from removing a standard AutoFilter applied directly to a worksheet range.

The following example uses the first table on the active worksheet. It clears active filters on the sheet before hiding that table’s buttons. Because ShowAllData is called on the worksheet, any other active filters on that same sheet are cleared too.

Sub HideFirstTableFilterButtons()
    Dim ws As Worksheet
    Dim tbl As ListObject

    Set ws = ActiveSheet

    ' Stop if the active sheet has no tables.
    If ws.ListObjects.Count = 0 Then Exit Sub

    Set tbl = ws.ListObjects(1)

    ' Clear active filtering on this worksheet, if needed.
    If ws.FilterMode Then
        ws.ShowAllData
    End If

    ' Hide the selected table's filter buttons.
    tbl.ShowAutoFilter = False
End Sub

If a sheet has several tables, ws.ListObjects(1) means the first table in the worksheet’s collection, not necessarily the one a user has in mind. Select the intended table by its name instead, for example with Set tbl = ws.ListObjects("Table1"). Hiding a table’s buttons is a display change; it does not convert the table to a normal range. The table remains a table, with its structured references and other table features.

Choose the right operation

Before writing a macro, decide whether the goal is to reveal rows, remove controls, or change the data structure. These actions are not interchangeable:

  • Show every row, retain filtering controls: check FilterMode and call ShowAllData.
  • Remove standard worksheet filter arrows: clear active criteria if needed, then set AutoFilterMode to False.
  • Hide a table’s filter buttons: set that table’s ShowAutoFilter property to False.
  • Remove the table itself: use Excel’s table-conversion or deletion features only if changing the data structure is intended. Hiding arrows does not do that.

For a repeatable report, clearing criteria while keeping arrows is often less disruptive. For a finished presentation sheet, removing or hiding arrows may make the layout cleaner. If users still need to filter a table later, keep its buttons visible or provide another deliberate way to restore them.

Run the macro safely

VBA changes the workbook directly, and macros cannot be undone with Ctrl+Z. Save a copy of the workbook before running a macro, especially if it will run across several sheets or is part of a larger automation. Review the selected worksheet and any table name in the code before execution.

  1. Save a backup copy of the workbook.
  2. Save the macro-enabled file as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the VBA code.
  3. Open the Visual Basic Editor. In Windows Excel, press Alt+F11, or use Developer > Visual Basic. If the Developer tab is hidden, enable it through Excel Options > Customize Ribbon.
  4. In the editor, choose Insert > Module, paste the procedure into the new standard module, and return to Excel.
  5. Run it from the editor with the cursor in the procedure and press F5, or use Developer > Macros, select the macro, and choose Run.

On a Mac, open the Visual Basic Editor from Tools > Macro > Visual Basic Editor; depending on the keyboard and function-key settings, the shortcut may require Option+F11 or Fn+Option+F11. The Windows ribbon path and keyboard shortcuts do not always match Mac. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop Excel application for these procedures.

Common errors and misconceptions

A common error is calling ShowAllData unconditionally. If there is no active filter, Excel may report that the ShowAllData method of the Worksheet class failed. Checking FilterMode first avoids that common case. If a macro still fails, verify that the worksheet reference points to the intended sheet and that the data actually has an active filter.

Another misconception is that removing arrows deletes filtered-out records. A filter normally hides rows from view; it does not delete those records. Likewise, ShowAllData reveals rows but does not remove the AutoFilter controls. If rows were manually hidden, clearing a filter may not unhide them; manual row hiding is a separate state.

Also check whether the data is a table. Setting ws.AutoFilterMode = False is intended for a worksheet AutoFilter, whereas a table’s buttons are managed with tbl.ShowAutoFilter. A workbook can contain both tables and ordinary filtered ranges, so identify the relevant object instead of applying one property indiscriminately.

Frequently Asked Questions

How do I clear an Excel filter with VBA without removing the arrows?
Use If ws.FilterMode Then ws.ShowAllData. The check prevents a common error when no filter is active, and the AutoFilter arrows remain available.

How do I remove the filter dropdown arrows with VBA?
For a standard worksheet AutoFilter, clear any active filter if needed and set ws.AutoFilterMode = False. For an Excel table, set its ShowAutoFilter property to False.

Does removing AutoFilter delete rows that were filtered out?
No. Filtering generally hides rows rather than deleting them. Clearing the filter displays the matching and nonmatching rows again, unless they were separately deleted or hidden manually.

Why does ShowAllData give me an error?
A frequent cause is that no filter is currently active. Check ws.FilterMode before calling ws.ShowAllData. Also confirm that ws refers to the sheet containing the filtered data.

Can I run VBA to remove AutoFilter in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the code; the Mac editor and shortcut details can differ from Windows.

Related

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.