Excel VBA AutoFilter Off: Clear Filters or Remove Arrows

VBA & macros · Updated November 21, 2026

To turn off a standard worksheet AutoFilter in Excel VBA, first clear any active filter so the filtered-out rows reappear, then set the worksheet’s AutoFilterMode property to False. These are two different actions: clearing a filter shows all records but keeps the dropdown arrows, while turning off AutoFilter removes the arrows.

Two meanings of “AutoFilter off”

“Turn AutoFilter off” can mean either of two things. You might want to display every row while leaving the header dropdowns available, or you might want to remove the dropdowns as well. Choosing the right action matters: the first is useful when someone needs to review all records but may filter them again; the second is appropriate when the worksheet should no longer have filter controls.

  • Clear the current filter: show the records hidden by filter criteria, while retaining the filter arrows.
  • Remove AutoFilter: remove the arrows from the worksheet’s filtered range.
  • Do both: clear the filter first, then remove the arrows. This is the usual interpretation when a macro needs to leave a plain, fully visible data range.

A filter does not delete records. It temporarily hides rows that do not match the selected criteria. For example, filtering a sales list to show only “North” hides other regions from view; clearing the filter brings them back. Removing the dropdowns is a separate change to the filter interface.

What the VBA properties mean

For a worksheet AutoFilter, Worksheet.FilterMode indicates whether the worksheet is currently displaying a filtered view. It is read-only, so use it to check the state before calling a method that expects a filter to be active. Worksheet.AutoFilterMode indicates whether AutoFilter arrows are displayed, and VBA lets you set this property to False to turn those arrows off.

The properties are independent. A worksheet can have arrows with no criteria currently hiding rows, or arrows while a filter is actively hiding rows. That is why checking only whether the arrows are present does not tell a macro whether it needs to reveal filtered records. Similarly, clearing the filter does not, by itself, remove the arrows.

ShowAllData is the method used to show all records in an active filtered view. It does not remove the AutoFilter controls. The reliable sequence for the “show everything and remove the arrows” result is therefore: check FilterMode, call ShowAllData if needed, and then set AutoFilterMode to False.

VBA to clear filters and remove arrows

The following macro targets an ordinary worksheet AutoFilter on a sheet named Data in the workbook containing the macro. Change the sheet name to match your workbook. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub ClearFiltersAndRemoveArrows()
    Dim ws As Worksheet

    ' Use the named sheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Reveal rows hidden by an active filter, if there is one.
    If ws.FilterMode Then
        ws.ShowAllData
    End If

    ' Remove the worksheet AutoFilter arrows.
    ws.AutoFilterMode = False
End Sub

The If check is important. Calling ShowAllData when there is no active filtered view can cause a run-time error. With the check, the macro can run when the sheet is already showing all records as well as when a filter is hiding some rows.

Using ThisWorkbook makes the target explicit: it means the workbook that stores the VBA code. If the macro is stored in a personal macro workbook but should operate on the workbook currently selected in Excel, you may instead need ActiveWorkbook. Be deliberate about that choice; an active workbook can change, and an accidentally targeted workbook may not contain the sheet named in the code.

Clear a filter but keep the dropdown arrows

If the goal is only to reveal every record and leave the filter controls available, omit the line that sets AutoFilterMode to False. For a sheet named Data, the guarded code is:

Sub ClearWorksheetFilter()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Only call ShowAllData when a filter is actively hiding rows.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

This is often the better choice for reusable reports. The recipient sees the complete list, and the dropdowns remain ready for another selection. Clearing criteria is also different from resetting sort order: a sort rearranges rows, and clearing a filter does not necessarily restore their original order. If the original order matters, include a sequence or ID column in the data so it can be sorted back into place.

Worksheet ranges and Excel tables are different

The worksheet-level code above is intended for a normal filtered range, such as a list with header arrows added through Data > Filter. Excel tables, created with Insert > Table or Home > Format as Table, have their own filter controls and table properties. A table also has structured columns and automatically expands as data is added, so removing its filter buttons is not the same as converting the table into a plain range.

For a table, clear a particular filter from its header dropdown, or use the Data tab’s Clear command to clear filters on the worksheet. To hide a table’s filter buttons with VBA, use the table’s ShowAutoFilter property. For example, change “SalesTable” to the actual table name:

Sub HideTableFilterButtons()
    Dim ws As Worksheet
    Dim lo As ListObject

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

    ' Hide the table's header filter buttons.
    lo.ShowAutoFilter = False
End Sub

This example hides the buttons; it is not a substitute for explicitly clearing criteria when the goal is to show all table rows. If a table is involved, confirm whether the request is to clear its current selection, hide its buttons, or convert the table to a regular range. Those are distinct operations, and the last one changes the table structure.

Using the ribbon, and running VBA on Mac or the web

For a one-time change, the ribbon may be simpler than a macro. In Excel for Microsoft 365 on Windows, use Data > Clear to clear filter criteria while retaining the filter controls. Use Data > Filter to toggle filtering controls for a selected range. The exact effect of toggling the ribbon control depends on the selected range and whether it is an ordinary range or a table, so check that the rows you need are visible afterward.

Excel for Mac has the same core VBA properties and methods in the desktop app, although ribbon layout and keyboard shortcuts can differ. Excel for the web does not run VBA macros; use its available filter controls or open the workbook in the desktop application to run VBA. If a workbook is saved as a macro-enabled file, use the .xlsm format and allow macros only when the workbook and its code are trusted.

Common problems and safer troubleshooting

  • “ShowAllData method of Worksheet failed.” The worksheet may not currently be in FilterMode. Check ws.FilterMode before calling the method, as in the examples.
  • The macro says a worksheet or table was not found. Confirm the exact sheet tab name and, for a table, its name in Table Design. A sheet’s visible tab name and a table’s name are different identifiers.
  • Some rows remain hidden. They may have been hidden manually, hidden by grouping, or excluded for a reason unrelated to AutoFilter. Clearing a filter is not a general command to unhide every row. Inspect the row headers and use the relevant unhide or outline controls if necessary.
  • The arrows disappear but the data still looks incomplete. Check for manually hidden rows, another filtered range, or a table filter. Removing one worksheet AutoFilter does not guarantee that every other hiding mechanism on the sheet has been cleared.
  • The macro changes the wrong sheet. Qualify the worksheet through a specific workbook object, as shown, rather than relying on whichever sheet happens to be active.

Avoid using Rows.Hidden = False as a shortcut for clearing a filter. That can reveal rows deliberately hidden for layout or confidentiality reasons and may not express the intended filter operation. Likewise, do not assume that removing arrows deletes the underlying data: records remain in the worksheet unless separate code deletes them.

Frequently Asked Questions

How do I turn off AutoFilter in Excel VBA?
For an ordinary worksheet range, use ws.AutoFilterMode = False to remove the arrows. If a filter is active and you also want all filtered-out records shown, first call ws.ShowAllData when ws.FilterMode is True.

How do I clear a filter but keep the arrows?
Check ws.FilterMode and call ws.ShowAllData if it is True. Do not set AutoFilterMode to False if you want the dropdown arrows to remain.

Does turning off AutoFilter delete rows?
No. AutoFilter hides rows that do not match the selected criteria; it does not delete those records. Removing filter controls is not the same as deleting data, though separate code in a larger macro could of course delete rows.

Why does ShowAllData give me an error?
It commonly fails when there is no active filtered view to clear. Test FilterMode first and call ShowAllData only when it is True.

Does AutoFilterMode = False work on Excel tables?
Tables manage filter buttons through table-specific behavior. To hide a table’s buttons, use its ShowAutoFilter property; clear its criteria separately when you need all table rows displayed. Do not confuse hiding the buttons with removing the table or its data.

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.