VBA: Clear an AutoFilter If It Exists Safely

VBA & macros · Updated November 18, 2026

Yes. In Excel VBA, check whether the worksheet is currently filtered with Worksheet.FilterMode, then call ShowAllData only when it is: If ws.FilterMode Then ws.ShowAllData. This clears the active filter criteria and shows all rows; it does not remove the filter’s dropdown arrows.

What “clear AutoFilter” means

People use “clear AutoFilter” to mean two different things: show every row again while keeping the filter controls, or remove the filter controls altogether. These are separate actions in Excel VBA. Knowing which result you want matters because a macro that removes the arrows can change how a worksheet is laid out for the next person using it.

When a filter is applied, Excel hides rows that do not match the selected criteria. Those rows are not deleted; clearing the criteria makes them visible again. The dropdown arrows may remain so you can apply another filter. If you also want those controls gone, you need a separate step, and the right method depends on whether the filter belongs to an ordinary cell range or an Excel table.

Clear a worksheet filter only when one is active

For a worksheet-level AutoFilter, FilterMode tells you whether the sheet is currently filtering rows. It is the useful guard for ShowAllData: calling that method when no filter is active can cause a run-time error. This short procedure avoids that situation and leaves any dropdown arrows in place.

Sub ClearWorksheetFilterIfActive()
    Dim ws As Worksheet

    ' Change Data to the name of the worksheet to check.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Show every row only when this worksheet is filtered.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

Replace Data with the worksheet’s tab name. ThisWorkbook means the workbook containing the macro. If the code is stored in a personal macro workbook or another file, use an explicitly referenced workbook instead, such as Workbooks("Report.xlsx"), and confirm that workbook is open. The worksheet name in a VBA reference is its tab name, not necessarily its CodeName.

The test is intentionally about the current filtered state, not whether filter arrows exist. A worksheet can have AutoFilter arrows showing while no criteria are active. In that case, FilterMode is false and the macro does nothing, which is usually the desired behavior for a “clear if exists” routine.

Clear criteria while keeping dropdown arrows

For a normal range with AutoFilter enabled, ws.ShowAllData makes the filtered-out records visible and clears the active selection criteria. It does not turn off AutoFilter. The dropdown arrows remain available for later use, and the range can be filtered again without rebuilding the controls.

For example, suppose a worksheet has a filter on a sales list and currently displays only rows for the East region. Running the guarded code displays all regions again. It does not delete the East-region records, alter their values, or remove the header-row dropdowns. It also does not clear cell contents or reset unrelated worksheet formatting.

This is generally the best choice for a workbook that should open with all records visible but retain interactive filtering. It is also safer than calling ShowAllData unconditionally, because the check prevents an avoidable error when the sheet is already showing all rows.

Remove the filter arrows from an ordinary range

If your goal is to remove the worksheet’s AutoFilter controls as well as show all rows, first clear any active criteria and then turn off the worksheet AutoFilter mode. Use this pattern for an ordinary filtered range, not as a way to hide the built-in controls on a table.

Sub ClearAndRemoveRangeFilter()
    Dim ws As Worksheet

    ' Change Data to the worksheet with the range filter.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Restore any rows hidden by filtering.
    If ws.FilterMode Then
        ws.ShowAllData
    End If

    ' Remove worksheet-level AutoFilter dropdown arrows, if present.
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
End Sub

AutoFilterMode indicates whether AutoFilter arrows are displayed on the worksheet. Unlike FilterMode, it can be true when no filter criteria are active. Setting it to false removes the worksheet-level arrows. Because a worksheet can contain more than one kind of data structure, do not use this step casually on a sheet where users rely on filter controls.

Excel tables need table-aware handling

An Excel table, created with Insert > Table, has its own AutoFilter controls. If you want to clear a particular table without affecting other filtered data on the sheet, refer to that table by its name and inspect its filter fields. The following example clears the table’s criteria only if at least one field has a filter applied.

Sub ClearTableFilterIfActive()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim fieldIndex As Long
    Dim hasActiveFilter As Boolean

    ' Change the worksheet and table names to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    ' Look for an active criterion in any table filter field.
    For fieldIndex = 1 To lo.AutoFilter.Filters.Count
        If lo.AutoFilter.Filters(fieldIndex).On Then
            hasActiveFilter = True
            Exit For
        End If
    Next fieldIndex

    ' Clear this table's criteria while retaining its filter controls.
    If hasActiveFilter Then
        lo.AutoFilter.ShowAllData
    End If
End Sub

Change SalesTable to the table’s actual name. You can find or change a table name by selecting a cell in the table and using the Table Design tab’s Table Name box. The table-specific approach is helpful when a worksheet has several tables or other filtered ranges and you want to target one object rather than clear the active worksheet filter state generally.

To hide a table’s dropdown arrows, use its ShowAutoFilter property, rather than relying on the worksheet-level arrow switch. Hiding a table’s arrows is a display choice; it is distinct from clearing criteria. If you need both outcomes, clear the table’s active criteria first and then set lo.ShowAutoFilter = False. Consider whether users should still be able to filter the table before hiding those controls.

How to choose the right method

Use the narrowest method that matches the object and result you intend. A worksheet-level call is convenient for a single filtered range. A table-level call is clearer when the target is a named table, especially if other filtered objects share the worksheet. Choose whether to retain arrows separately from whether to clear the criteria.

  • Show all rows and keep ordinary range arrows: check ws.FilterMode, then call ws.ShowAllData.
  • Show all rows and remove ordinary range arrows: clear active criteria first, then set ws.AutoFilterMode = False.
  • Clear one named table: check its filter fields, then call the table AutoFilter’s ShowAllData method.
  • Hide a table’s arrows: use the table’s ShowAutoFilter property; do not confuse this with clearing its criteria.

Also consider what the workbook should look like after the macro runs. For a report template, keeping arrows may be useful because recipients can explore the data. For a file being prepared for printing or distribution, you may prefer to clear criteria and remove controls, but check the result on a copy first.

Common problems and how to avoid them

A frequent error is calling ShowAllData without checking whether filtering is active. Guarding the call with FilterMode avoids that failure for worksheet-level filtering. Another common misunderstanding is expecting the arrows to disappear after ShowAllData; that method clears criteria, not the AutoFilter controls.

  • The macro points at the wrong sheet: verify the tab name and workbook reference. An unqualified ActiveSheet can point to a different sheet if the user changes selection.
  • The macro clears more than intended: worksheet-level methods are broader than a named-table operation. Use a table reference when the goal is to target one table.
  • Rows still appear missing: they may be hidden manually, hidden by outline grouping, or concealed by another feature. Clearing AutoFilter criteria only restores rows hidden by that filter.
  • The code removes controls from a table: tables manage their own filter buttons. Use the table’s properties when working with a ListObject.
  • A filter is applied to a different range: check the worksheet’s actual filtered range or the table name before running a broader procedure.

Do not use On Error Resume Next as a substitute for checking the filter state. It can suppress unrelated errors, such as a misspelled worksheet name, making the macro appear to succeed when it has not done the intended work. Explicit checks make the reason for each action easier to understand and maintain.

Run the macro safely

Before running a macro that changes a workbook, save a copy. VBA actions cannot be undone with Ctrl+Z, even when the visible change seems small. In Excel for Microsoft 365 on Windows, save a macro-enabled workbook as .xlsm if the VBA code needs to remain in the file. Open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure there. Run it from the editor with the cursor inside the procedure and press F5, or run it through Developer > Macros.

On Mac, the Visual Basic Editor can generally be opened with Option+F11; depending on the keyboard, you may also need Fn. Ribbon layouts and shortcut behavior can vary by Mac configuration, so use the Developer tab or Excel’s Tools > Macro menu if the shortcut is unavailable. Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros; use the desktop Excel app. Macro security settings may also require you to enable macros for a trusted file.

Frequently Asked Questions

Does ShowAllData remove AutoFilter arrows?

No. ShowAllData clears the active criteria and displays all rows, but leaves the filter controls in place. For an ordinary worksheet range, set AutoFilterMode to false if you also want to remove those arrows.

What is the difference between FilterMode and AutoFilterMode?

FilterMode indicates that the worksheet is currently filtering data. AutoFilterMode indicates that AutoFilter dropdown arrows are displayed. A sheet can have arrows without actively hiding rows, so these properties answer different questions.

Can I clear a filter without deleting hidden rows?

Yes. Clearing the criteria with ShowAllData reveals rows hidden by the filter; it does not delete them. If rows remain hidden afterward, check for manual row hiding or another feature such as outline grouping.

How do I clear only one table’s filter?

Reference the table through Worksheet.ListObjects, check whether any of its filter fields are on, and call that table’s AutoFilter ShowAllData method. This is more targeted than clearing worksheet-level filter state.

Why does ShowAllData sometimes cause a VBA error?

The method can fail when there is no active filter to clear. Test ws.FilterMode first for a worksheet-level filter, or check the relevant table’s filter fields before calling its AutoFilter method.

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.