Excel VBA AutoFilter: Show All Data

VBA & macros · Updated November 21, 2026

To show every row hidden by an Excel AutoFilter, use VBA’s ShowAllData method, but first check that the worksheet is actually filtered: If ws.FilterMode Then ws.ShowAllData. This clears the active filter criteria while leaving the filter drop-down arrows in place; it does not remove the AutoFilter itself.

What “Show All Data” Does

An AutoFilter narrows a list by hiding rows that do not match selected criteria. A filter might show only one region, a date range, or records whose status is “Open.” The rows that fail the criteria are not deleted; they are simply excluded from the visible results. Showing all data makes those filtered-out rows visible again.

In VBA, ShowAllData is available through worksheet and AutoFilter objects. The method clears the criteria for the filtered list. It is different from turning AutoFilter off: when the criteria are cleared, users can still open the filter arrows and apply another filter without setting them up again.

That distinction matters in many everyday macros. A report-refresh button may need to reset filters before displaying a complete dataset, while a worksheet prepared for users may need its filter controls left available. In the latter case, clearing criteria is usually the right action.

A Safe Worksheet-Level Macro

For a normal filtered range on a known worksheet, the following pattern is concise and avoids calling the method when there is no active filter. Before running any macro, save a copy of the workbook: VBA actions cannot be reversed with Ctrl+Z.

Sub ShowAllRowsOnDataSheet()
    Dim ws As Worksheet

    ' Refer to the intended worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Data")

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

Replace Data with the worksheet’s actual tab name. ThisWorkbook means the workbook containing the macro, which is often safer than ActiveWorkbook: the active workbook can change while a macro is running. If your code is stored in the Personal Macro Workbook or an add-in, however, ThisWorkbook refers to that file, so you may need to set ws to the intended workbook explicitly.

The test is important because calling ShowAllData when no list is filtered commonly raises run-time error 1004. An AutoFilter can be present with its arrows showing even when no criteria are active. In that situation, FilterMode is false, so the guarded macro does nothing and leaves the arrows alone.

Clearing Filters on a Specific Excel Table

If the data is formatted as an Excel table, target the table’s AutoFilter rather than relying on the worksheet’s general filter state. This is especially useful when a sheet contains multiple tables and you want to reset only one of them.

Save a copy before running the macro, because the change cannot be undone with Ctrl+Z.

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

    ' Identify the worksheet and table by their names.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    ' Clear this table's criteria only if it is filtered.
    If lo.AutoFilter.FilterMode Then lo.AutoFilter.ShowAllData
End Sub

Change SalesTable to the table’s actual name. To find it, click inside the table and look for its name on the Table Design tab. A table’s name is not necessarily the same as the worksheet name. This object-specific approach helps avoid accidentally clearing criteria on a different filtered list.

If you have an ordinary filtered range rather than a table, use the worksheet-level example or refer to the worksheet’s AutoFilter object. The correct object depends on how the data was set up; a table is a ListObject, while a range with filter arrows is not.

Show All Data Versus Removing Filter Arrows

People often say “turn off the filter” when they mean either clear the selected criteria or remove the controls. These are separate operations in Excel:

  • Show all data: clears filter criteria and displays the list’s rows, while leaving the filter arrows available.
  • Remove AutoFilter arrows: turns off the filtering controls for a range or table. This changes the worksheet setup rather than simply resetting the current view.

For a normal worksheet AutoFilter, ws.AutoFilterMode = False turns off the arrows. Do not use that line when the goal is just to reveal all records and let users filter again. A table has its own filtering controls and related table options, so treat it as a table rather than assuming a worksheet-level setting is the best way to manage it.

Another related property is FilterMode. It reports whether the worksheet is currently filtering a list; it does not tell you simply whether arrows are visible. AutoFilterMode, by contrast, indicates whether AutoFilter arrows are displayed and can be set to turn them off. Confusing these two properties is a frequent reason a macro behaves differently from what its author intended.

Which Approach Should You Choose?

Choose the narrowest method that matches the data you want to reset. That makes the macro easier to understand and reduces the chance of changing an unrelated view.

  • One ordinary filtered range on a sheet: check ws.FilterMode and call ws.ShowAllData.
  • One named table among several: use lo.AutoFilter.FilterMode and lo.AutoFilter.ShowAllData for that table.
  • Only want to remove the arrows: turn off AutoFilter rather than calling ShowAllData.
  • Want to remove one column’s criterion only: use a field-specific AutoFilter operation instead of clearing every criterion. Confirm the field number refers to the intended column in the filtered range.

For example, if a table is filtered to show “West” in Region and “Open” in Status, calling its ShowAllData method makes all its records visible by clearing both criteria. If the goal is to keep the Status restriction and remove only the Region restriction, clearing all criteria is too broad; use an operation aimed at the Region field instead.

Common Errors and Unexpected Results

Run-time error 1004: The code may be calling ShowAllData when there is no active filter, or may be targeting the wrong object. Add a state check such as If ws.FilterMode Then, and verify the worksheet or table name. Also make sure the macro is not referring to a different workbook than the one containing the data.

The arrows remain visible: That is expected. ShowAllData clears criteria, not the AutoFilter controls. If you wanted the arrows removed, use the appropriate AutoFilter-off action instead.

Some rows are still missing: Check whether those rows were hidden manually, whether outline grouping is collapsing them, or whether a different table or range has a filter. Clearing an AutoFilter is not a general command to unhide every row or expand every outline. Manually hidden rows and grouped rows may need their own visibility or outline action.

The wrong data changes: Avoid relying on ActiveSheet or Selection in a macro that should always work on a particular list. Explicitly set a worksheet and, for a table, a ListObject. If a sheet contains multiple tables, this makes the intended target unambiguous.

The macro will not run: Excel for the web does not run VBA macros. Use the desktop version of Excel for Microsoft 365 on Windows, or desktop Excel for Mac, and save the file in a macro-enabled format such as .xlsm if it needs to retain VBA. Macro security settings may also prevent a downloaded or untrusted file from running code.

Manual Options and Platform Notes

For a one-time reset, use Excel’s ribbon rather than writing a macro: select a cell in the filtered list, then choose Data > Sort & Filter > Clear. The command clears filtering criteria; it does not delete records. To remove or reapply filter arrows on a range, use Data > Sort & Filter > Filter. If the list is a table, its filter controls are part of the table interface as well.

VBA code for these methods is broadly the same in desktop Excel for Windows and Mac, though workbook security, menus, and keyboard shortcuts can differ. Excel for the web does not execute VBA. If a workbook is shared with people who use the web app, provide a manual route or another supported automation option rather than assuming a VBA button will work for everyone.

Frequently Asked Questions

How do I show all rows in Excel VBA?
Check whether the worksheet is filtered, then call ws.ShowAllData. For example, use If ws.FilterMode Then ws.ShowAllData after setting ws to the correct worksheet.

Does ShowAllData remove the filter arrows?
No. It clears the active criteria and shows all rows in the filtered list. The filter arrows remain so another filter can be applied.

Why does ShowAllData give me error 1004?
The most common cause is calling it when no filter criteria are active. Test FilterMode first, and check that the code refers to the worksheet or table containing the filtered list.

How can I clear filters from one table only?
Set a ListObject variable to the named table, then check its AutoFilter’s FilterMode and call that AutoFilter’s ShowAllData method. This is more precise than targeting a sheet with multiple tables.

Will ShowAllData unhide rows I hid manually?
Not necessarily. It clears AutoFilter criteria; it is not a universal unhide command. Manually hidden rows or rows concealed by outline grouping may need to be shown or expanded separately.

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.