VBA Turn Off AutoFilter in Excel Without Losing Data

VBA & macros · Updated November 18, 2026

To turn off a worksheet AutoFilter in VBA, first show all rows if a filter is active, then set the worksheet’s AutoFilterMode property to False. For an Excel table, clear its filter separately and set the table’s ShowAutoFilter property to False; hiding arrows alone does not necessarily reveal filtered-out rows.

What “turn off AutoFilter” can mean

Excel uses filters in two related ways: an AutoFilter applied to a regular cell range, and the filter controls built into an Excel table. In everyday conversation, turning off a filter might mean removing the dropdown arrows, clearing the selected criteria so every record is visible, or doing both. Those actions are not interchangeable in VBA.

The key distinction on a worksheet is between AutoFilterMode and FilterMode. AutoFilterMode is True when AutoFilter dropdown arrows are displayed. FilterMode is True when the worksheet is currently filtering records. A sheet can have arrows visible but no criteria restricting the rows, so FilterMode can be False while AutoFilterMode remains True.

For a regular range, ShowAllData reveals records hidden by the active filter but leaves the filter controls available. Setting AutoFilterMode to False removes the worksheet’s filter arrows. If you want to both reveal every row and remove the controls, do both operations, in that order. This does not delete the source data, but it does remove the active filter setup.

Clear criteria or remove the dropdown arrows?

Choose the action based on what the workbook should look like after the macro runs:

  • Show every record but keep filtering available: call ShowAllData when a filter is active. The arrows stay, allowing someone to apply a different filter immediately.
  • Remove the arrows and show every record: clear the active filter first, then set AutoFilterMode to False for a regular worksheet range.
  • Hide a table’s arrows: set the table’s ShowAutoFilter property to False. Clear any active table filter first if you also need all table rows visible.
  • Keep the filter applied but hide its controls: this is a different goal. Hiding table arrows does not reliably mean the filtered rows have been restored; explicitly clear criteria when full visibility matters.

Once a filter is cleared or removed, VBA does not preserve the prior criteria as a convenient setting to restore later. If users need to switch between filtered and unfiltered views, consider leaving the controls in place or writing a separate, well-defined routine to reapply the intended criteria.

VBA for a regular worksheet AutoFilter

This macro targets a regular worksheet AutoFilter, not the dropdowns belonging to Excel tables. It clears active criteria when needed and removes the sheet’s AutoFilter arrows. Change the worksheet name to match the workbook. Save a copy of the workbook before running any macro: VBA actions cannot be reversed with Ctrl+Z.

Sub TurnOffRangeAutoFilter()
    Dim ws As Worksheet

    ' Target the worksheet that contains the regular filtered range.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Reveal all rows if the worksheet is actively filtered.
    If ws.FilterMode Then ws.ShowAllData

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

The FilterMode check matters because ShowAllData can raise an error when there is no active filter to clear. The AutoFilterMode check makes the final line explicit and avoids assigning False when there are no arrows to remove. Qualifying the worksheet with ThisWorkbook is safer than relying on ActiveSheet: a user can click another sheet before launching a macro, and ActiveSheet would then refer to the wrong one.

This routine removes the worksheet-level AutoFilter controls. If the data is formatted as a table, use the table-specific approach below rather than assuming this range routine will manage the table’s controls and criteria.

VBA for Excel tables

Tables, also called ListObjects in VBA, have their own AutoFilter and a ShowAutoFilter property. The following example visits every table on a named worksheet, clears active table filters, and then hides each table’s dropdown arrows. It leaves the table and its data intact.

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

    ' Set this to the worksheet containing the tables.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Clear active criteria, then hide each table's arrows.
    For Each tbl In ws.ListObjects
        If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
        tbl.ShowAutoFilter = False
    Next tbl
End Sub

If the table arrows should remain available, remove the line that sets tbl.ShowAutoFilter to False. The remaining line clears an active table filter while keeping the controls visible. A worksheet can contain more than one table, which is why the example loops through ws.ListObjects instead of assuming that one table is the only filtered object.

To hide arrows without clearing criteria, omit the ShowAllData line—but be deliberate. Rows can remain filtered out even though the controls are no longer visible. That can confuse people reviewing the sheet, so a workbook should make its filtered state clear through its design or documentation.

How to add and run the macro

  1. Save a backup copy of the workbook. If you want to retain VBA in the working file, save it as an Excel Macro-Enabled Workbook (*.xlsm).
  2. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, Option+F11 is the common shortcut; depending on the keyboard, you may also need the Fn key.
  3. In the editor, choose Insert > Module, then paste the appropriate procedure into the new module.
  4. Change the worksheet name in the Set ws line to the actual tab name. Names must match, including spaces and punctuation.
  5. Return to Excel and run the macro from the Macro dialog or the Visual Basic Editor. Review the result before saving over your backup.

Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. Use the desktop Excel application for these procedures. On Windows, macros may also be blocked by Trust Center settings or an organization’s security policy; only enable macros in workbooks you trust.

Common problems and how to avoid them

  • Run-time error when clearing: ShowAllData should only be called when filtering is active. Keep the FilterMode check rather than calling it unconditionally.
  • The arrows disappear but some records are still hidden: the code likely hid controls without clearing the criteria, or it targeted a table differently from a normal range. Clear the relevant filter first.
  • The macro affects the wrong sheet: replace ActiveSheet with an explicit reference such as ThisWorkbook.Worksheets(“Data”). Confirm the workbook containing the code is the one you intend to change.
  • A table still shows filter arrows: table arrows are controlled by the table’s ShowAutoFilter property. Use the ListObject approach and check that the loop is running on the sheet containing the table.
  • The macro cannot change the sheet: worksheet protection can prevent changes to filtering or controls, depending on how protection was applied. Unprotect the sheet using the workbook’s authorized process, run the macro, and reapply protection if required.

Also distinguish filtering from hiding rows manually. ShowAllData clears filter criteria; it is not a general command to unhide every row hidden by a user or by another macro. If rows remain invisible after the filter is cleared, inspect row hidden states and any other code or outline settings that may be affecting the display.

Choosing the right method for your workbook

First identify whether the data is a table or a plain range. Click a data cell and look for the Table Design tab in the ribbon; if it appears, the selection is in a table. This is a useful check because a worksheet can contain both tables and a separate filtered range, and a macro aimed at one does not necessarily manage the other.

Next decide whether the objective is a clean view or a permanent removal of controls. For a one-time view of all records, clearing criteria while leaving arrows is usually the least disruptive choice. If a report should no longer be filterable, remove the relevant controls as well. When several filtered areas exist, scope the macro narrowly and test it on a copy: a broad operation can affect more than the data region a user had in mind.

In the Excel interface, the Data > Filter command toggles filter controls for a selected range, while table controls are associated with the table. The VBA properties make the distinction more explicit: worksheet AutoFilterMode concerns arrows on the sheet, and a table’s ShowAutoFilter controls that table’s arrows. Macros are useful when the same cleanup must be repeated, but for a single change the ribbon may be simpler.

Frequently Asked Questions

Does AutoFilterMode = False clear the filter criteria?
It removes worksheet AutoFilter arrows, but it is not the clearest way to guarantee that all filtered-out rows are shown. Check FilterMode and call ShowAllData first when the goal is to reveal every record.

How do I remove filter criteria but keep the arrows?
For a regular worksheet AutoFilter, call the worksheet’s ShowAllData method only when FilterMode is True. For a table, use its AutoFilter.ShowAllData method when the table’s AutoFilter.FilterMode is True. Both approaches clear the active filter while retaining the controls.

Can I turn off AutoFilter on every worksheet at once?
Yes, a macro can loop through the workbook’s worksheets, but a workbook may contain protected sheets, tables, or multiple filtered areas. A blanket routine needs separate handling for those cases. Start with the specific sheets and tables you mean to change rather than applying an untested workbook-wide macro.

Will turning off AutoFilter delete rows?
No. Removing filter controls does not delete the records; filtering changes which rows are displayed. Still, save a backup before running VBA, especially if the procedure performs other actions in addition to changing filter settings.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to use the Visual Basic Editor and run these procedures.

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.