VBA AutoFilter Off: Remove Arrows or Clear Filters

VBA & macros · Updated November 18, 2026

To show every row while keeping the filter controls, use VBA’s ShowAllData method when the worksheet is filtered. To remove worksheet AutoFilter arrows, set AutoFilterMode to False; for an Excel table, use its ShowAutoFilter property instead. These are different actions, so choose the one that matches what “AutoFilter off” means in your workbook.

What “AutoFilter off” means in Excel VBA

Excel’s AutoFilter lets readers narrow a list by choosing values or conditions from drop-down arrows in the header row. A request to switch it off can mean at least two things: clear the selected criteria so hidden rows return, or remove the drop-down arrows so users cannot filter from that range. A workbook can also contain a formatted Excel table, which has its own filter controls.

VBA exposes separate properties and methods for these states:

  • Worksheet.FilterMode reports whether the worksheet is currently showing a filtered result.
  • Worksheet.ShowAllData clears active filter criteria and displays all rows in the filtered list or lists on that worksheet.
  • Worksheet.AutoFilterMode reports whether worksheet AutoFilter arrows are displayed. Setting it to False removes those arrows.
  • ListObject.ShowAutoFilter controls whether a table displays its filter buttons.

The distinction matters because a filter can be active while its controls are hidden, and arrows can be visible even when no rows are currently excluded. Decide whether the goal is to reveal data, remove controls, or do both before choosing a macro.

Clear filter criteria but keep the arrows

If the goal is to restore all rows while leaving the header drop-downs available, check FilterMode and then call ShowAllData. The check is useful because calling ShowAllData when no filter is active can raise a run-time error. This example targets a worksheet named Data:

Sub ClearWorksheetFilters()
    Dim ws As Worksheet

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

    ' Clear criteria only when the worksheet is filtered.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

This displays all rows again but leaves the filter buttons in place. It is a good choice for a reset button on a report, or before a macro processes the complete dataset rather than only visible records. Note that ShowAllData clears filtering on the worksheet; if several filtered lists or tables are present, check the resulting sheet carefully rather than assuming it targets only one field.

To clear one particular field while retaining other criteria, use that field’s filter controls or write code that reapplies the desired remaining criteria. A general ShowAllData call is a full reset, not a selective reset of just one column.

Remove AutoFilter arrows from a worksheet range

For a standard AutoFilter range, set the worksheet’s AutoFilterMode property to False. The check avoids making an unnecessary change when the arrows are already absent:

Sub RemoveWorksheetFilterArrows()
    Dim ws As Worksheet

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

    ' Remove worksheet AutoFilter arrows if they are displayed.
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If
End Sub

This removes the worksheet AutoFilter controls. It is not the same instruction as ShowAllData, which clears criteria and restores rows. If your requirement is both to reveal all records and to remove the arrows, do both operations in that order: first call ShowAllData when FilterMode is true, then set AutoFilterMode to false when it is true.

Be careful if the worksheet contains a structured Excel table. Table filter buttons are controlled separately, so changing the worksheet property may not be the right way to manage that table’s controls. Use the table-specific property below instead.

Turn off filter buttons for an Excel table

An Excel table, also called a ListObject in VBA, has its own setting for displaying filter buttons. Identify the table by its name, which you can find by selecting a table and opening the Table Design tab. The following example hides the buttons for a table named SalesTable on the Data worksheet:

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

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

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

To display them again, set tbl.ShowAutoFilter = True. Hiding the buttons is a presentation and interaction change; do not assume it has cleared existing criteria. If the aim is to reveal every row as well, clear the active filter first. A worksheet-level ShowAllData call can clear active filtering on that sheet, so consider whether other lists on the same sheet should also be reset.

When a table name is misspelled or the table is on a different worksheet than expected, the line that assigns tbl can fail. Use the actual table name and worksheet name, and make sure the workbook containing the code is the workbook you intend to change.

Choose the right action for the result you need

Before running a macro, match the intended result to the relevant VBA member. This avoids a common mistake: removing the buttons when the real need is to show hidden rows, or clearing every criterion when only the controls need to disappear.

  • Show all rows and keep the controls: use If ws.FilterMode Then ws.ShowAllData.
  • Remove standard worksheet filter arrows: use If ws.AutoFilterMode Then ws.AutoFilterMode = False.
  • Hide a table’s filter buttons: set the relevant table’s ShowAutoFilter property to False.
  • Reset rows and remove controls: clear active criteria first, then turn off the appropriate arrows for the range or table.

For example, if a report should open with every record visible but users should still be able to filter, the first option is usually appropriate. If a finished presentation should not expose filter controls, remove the controls instead. If the workbook will be reused as a data-entry template, consider whether hiding controls will make it harder for users to inspect the data later.

Common errors, safety, and running the macro

A frequent run-time error occurs when code calls ShowAllData despite there being no active filter. The FilterMode guard in the example prevents that case. Another common problem is pointing the macro at the wrong worksheet or table: an unqualified reference such as ActiveSheet can silently affect whichever sheet happens to be selected, so explicit worksheet references are generally easier to review.

Other practical checks include:

  • Confirm that the data has a header row and that the filter is attached to the expected range or table.
  • Check that the table name is correct and that it belongs to the worksheet referenced in the code.
  • Remember that clearing filters can reveal records that were intentionally hidden from a report view. Review the output before sharing or printing it.
  • Save the workbook as a macro-enabled file, such as .xlsm, if it needs to retain VBA code.

On Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. You can insert a standard module from the editor using Insert > Module, paste the macro, and run it with the cursor inside the procedure by pressing F5. You may also run a macro from Excel through View > Macros > View Macros. On Mac, the VBA editor shortcut is commonly Option+F11; some keyboards require Fn+Option+F11. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop Excel app.

Save a copy of the workbook before running any macro that changes filters or visibility. VBA actions cannot be undone with Ctrl+Z, and a macro may change more than the currently selected range if it is written to operate on a whole worksheet.

Frequently Asked Questions

How do I clear an AutoFilter in VBA without removing the arrows?
Check whether the worksheet is filtered with ws.FilterMode, then call ws.ShowAllData. This restores the rows while leaving the filter controls available.

How do I turn off AutoFilter arrows in VBA?
For a standard worksheet AutoFilter, set ws.AutoFilterMode = False if ws.AutoFilterMode is true. For an Excel table, set that table’s ShowAutoFilter property to false.

Why does ShowAllData cause an error?
It commonly happens when there is no active filter to clear. Test ws.FilterMode first and call ShowAllData only when that property is true.

Does AutoFilterMode work for Excel tables?
Tables have their own filter-button setting: ListObject.ShowAutoFilter. Use the table’s property when your goal is to show or hide its buttons, and use a guarded ShowAllData call when your goal is to clear active criteria.

Can I run VBA AutoFilter code in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app for Windows or Mac to run the code.

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.