VBA Reset AutoFilter: Clear Filters, Keep the Setup

VBA & macros · Updated November 18, 2026

To reset an Excel AutoFilter in VBA while keeping its dropdown arrows, clear the active filter rather than turning AutoFilter off: use If ws.FilterMode Then ws.ShowAllData. This shows the filtered-out records again and leaves the filter controls in place; use AutoFilterMode = False only when you want to remove the arrows.

What “reset AutoFilter” means

People use “reset” to mean two different things in Excel. They may want to clear filter criteria so every row is visible, or they may want to remove the AutoFilter controls entirely. Those actions are not interchangeable. For the usual task—show all records again but leave the dropdowns available—ShowAllData is the appropriate method.

AutoFilter hides rows that do not meet the selected criteria; it does not delete those records. Clearing the filter restores them to view. It also clears the current filtering criteria, so Excel no longer has that selection applied. It does not undo a sort or restore the order the data had before sorting. If you need a particular order after resetting, apply the desired sort separately.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is useful if the code runs on the wrong sheet or clears more filters than intended.

Clear filters and keep the dropdown arrows

In Excel for Microsoft 365 on Windows, this short macro clears an active filter on the worksheet that is currently selected. It checks FilterMode first because calling ShowAllData when no filter is active can cause a run-time error. Select the worksheet you intend to clear before running it.

Sub ClearFiltersKeepArrows()
    Dim ws As Worksheet

    ' Use the selected worksheet.
    Set ws = ActiveSheet

    ' Clear criteria only when a filter is currently hiding rows.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

This is a worksheet-level reset: if the sheet contains more than one filtered range or a filtered Excel table, the call can clear the active filter state on that sheet, not just one particular field. Use the more targeted table example below when you need to limit the action to one table.

The macro does not require the filter arrows to be added or removed. If the sheet has arrows but no criteria are active, FilterMode is False and the macro simply exits. That makes it suitable for a button labelled “Show all” in a workbook where users may click it even when no filter is currently applied.

Reset one Excel table

Excel tables, also called ListObjects in VBA, have their own AutoFilter. If a worksheet contains several tables and you want to clear only one, refer to it by its table name. In this example, the table is named SalesTable; replace that name with the actual name shown on the Table Design tab.

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

    ' Find the table on the selected worksheet.
    Set ws = ActiveSheet
    Set lo = ws.ListObjects("SalesTable")

    ' Clear this table's criteria only if its filter is active.
    If lo.AutoFilter.FilterMode Then
        lo.AutoFilter.ShowAllData
    End If
End Sub

A table’s name is not necessarily the same as its worksheet name or the text in its header row. Click inside the table and check the Table Design tab for the Table Name box. If the name is misspelled or the table is on a different sheet, VBA will stop with an error when it tries to find it. This example also assumes the selected sheet actually contains the named table.

Use the worksheet-level macro instead if you want one command to clear every active filter on that worksheet. The targeted table version is preferable when a sheet has independent filtered sections and clearing all of them would surprise the user.

Clear filters across the workbook

To show all rows on every worksheet in the workbook containing the macro, loop through its worksheets and clear only those with an active filter. The code leaves filter arrows in place. It does not process chart sheets, which do not contain worksheet AutoFilters.

Sub ClearFiltersInThisWorkbook()
    Dim ws As Worksheet

    ' Check each worksheet in the workbook containing this macro.
    For Each ws In ThisWorkbook.Worksheets
        If ws.FilterMode Then
            ws.ShowAllData
        End If
    Next ws
End Sub

ThisWorkbook means the workbook where the VBA code is stored. That matters if a macro is stored in a personal macro workbook or another file: it may not be the workbook currently active on screen. If the intent is to process the currently active workbook instead, change the workbook reference deliberately and confirm the active file before running the macro.

A workbook-wide reset is convenient for a reporting file, but it can remove users’ current filter selections on every sheet. Consider whether that is the intended behaviour before assigning the macro to a prominent button or running it automatically when the workbook opens.

Clear filters or remove AutoFilter?

Excel exposes two related worksheet properties that are easy to confuse. FilterMode indicates that a filter is currently filtering data. AutoFilterMode indicates whether AutoFilter dropdown arrows are displayed. A sheet can have the arrows visible while no criteria are active, so the properties do not mean the same thing.

  • Show all records, keep arrows: use ws.ShowAllData when ws.FilterMode is True.
  • Remove the arrows: set ws.AutoFilterMode = False. This turns off the worksheet’s AutoFilter controls rather than merely clearing the current selection.
  • Restore a filter setup later: first identify the intended header-and-data range or table, then apply AutoFilter to that range. Removing arrows is not the same as clearing criteria.

For example, on a worksheet with a range-based AutoFilter, setting AutoFilterMode to False removes its filter arrows. If you later want to add them again, use the correct range, including its header row, rather than relying on whichever cells happen to be selected. This distinction also explains why calling Range.AutoFilter with no arguments is not a dependable “clear criteria” command: it is commonly used to toggle filter arrows, which is a different task.

Resetting filters from Excel’s interface

You do not need VBA for an occasional reset. In Excel for Microsoft 365 on Windows, select the filtered range or a cell in the table, then choose Data > Sort & Filter > Clear. This clears the current filter criteria while leaving the filtering feature available. To turn filtering off, use Data > Sort & Filter > Filter instead.

The names and placement of ribbon controls can vary slightly with window width and ribbon layout, but the Data tab’s Clear command is the relevant action for removing criteria. In Excel for Mac, use the Data tab’s filter controls; the precise ribbon layout can differ by release. Excel for the web can clear filters using its interface, but it cannot run VBA macros. Open the workbook in the desktop Excel app to run VBA.

For Windows, Ctrl+Shift+L toggles AutoFilter on or off for a selected range or table; it is not the same as the Clear command and may remove the arrows. Keyboard shortcuts on Mac differ by keyboard and Excel release, so use the Data tab when you specifically need to clear criteria rather than toggle filtering.

Common errors and troubleshooting

The most frequent error occurs when code calls ShowAllData even though there is no active filter. Checking FilterMode before the call avoids that situation. A second common source of errors is assuming that visible arrows mean that rows are currently filtered. Arrows can remain visible when every record is already shown.

  • “ShowAllData method of Worksheet class failed”: check whether a filter is actually active, and guard the call with If ws.FilterMode Then. Also confirm that ws refers to the intended worksheet.
  • “Subscript out of range” with a table macro: confirm the exact table name and that it exists on the selected worksheet.
  • The macro clears more than expected: a worksheet-level reset can affect more than one filtered object on that sheet. Use a table-specific reference if only one table should be cleared.
  • Some rows remain hidden: rows may have been hidden manually or by another feature, rather than by the AutoFilter criteria. Clearing an AutoFilter does not necessarily undo separate row-hiding actions or outline grouping.
  • The code runs on the wrong file: verify the selected sheet and whether the macro uses ThisWorkbook or ActiveWorkbook.

For a quick diagnosis, inspect the worksheet after the macro runs: are the filter arrows still present, are the expected records visible, and did the row order change? The arrows should remain after a clear-only reset, while a changed sort order would require a separate sort operation. If a macro is part of a shared workbook, make its scope clear in its button label or instructions.

When to use a macro

A macro is useful when clearing filters is a repeated step in a report, when one button should reset multiple sheets, or when the operation is part of a larger automation routine. It is less useful for a one-off action, where the Data tab’s Clear command is quick and avoids enabling macros.

On Windows, open the Visual Basic Editor with Alt+F11 on most keyboards, insert a standard module, and paste the procedure there. On a Mac, the editor shortcut depends on the keyboard and function-key settings; use Excel’s Visual Basic Editor command if the shortcut is unavailable. Save the workbook in a macro-enabled format such as .xlsm if it must retain VBA code. A workbook saved as .xlsx does not preserve its VBA project.

Macros may be blocked by organizational security settings, and users should only enable code they trust. Excel for the web does not execute VBA. These limits do not affect the manual Clear command, which is a practical alternative when a workbook must remain macro-free.

Frequently Asked Questions

How do I clear an AutoFilter without removing the dropdown arrows?
Use ShowAllData when the worksheet’s FilterMode is True. This clears the active criteria and restores filtered-out records while keeping the AutoFilter controls available.

What is the difference between FilterMode and AutoFilterMode?
FilterMode indicates that filtering is active. AutoFilterMode indicates that AutoFilter arrows are displayed. Arrows can be present even when no filter criteria are active.

Does clearing AutoFilter undo a sort?
No. Clearing criteria shows records hidden by the filter, but it does not reverse a sort. Apply a separate sort if you need to restore a particular order.

Can I reset only one column’s filter?
Yes. A worksheet-level ShowAllData clears the active filter criteria rather than just one field. To change a single column’s criteria, use that column’s dropdown or write VBA that reapplies the desired criteria for the specific field.

Can VBA clear filters in Excel for the web?
No. Excel for the web supports filter actions through its interface but does not run VBA macros. Open the workbook in desktop Excel to execute VBA.

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.