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.FilterModeand callws.ShowAllData. - One named table among several: use
lo.AutoFilter.FilterModeandlo.AutoFilter.ShowAllDatafor 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
- Excel Table Data Filtering: How to Narrow Rows
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Check If Worksheet Exists: Code and ExamplesTo check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s…
- VBA AutoFilter: What True Means and How to Use ItYes, VBA AutoFilter uses True in some related settings, but True is not a universal command to filter…
- Reset Used Range in Excel VBA: Safe Steps and CodeTo reset an inflated used range in Excel VBA, delete the unused rows below and columns to the…
- VBA MsgBox Font Size: What You Can and Cannot ChangeYou cannot set the font size of VBA’s built-in MsgBox in Excel: the function has no font-size argument…
- Excel VBA: Open a Workbook Using a Variable File NameTo open an Excel workbook whose name or path is stored in a VBA variable, pass that variable…
- VBA Function List: Built-In Functions, Groups, and UseA VBA function list is a reference to built-in functions you can call from Visual Basic for Applications,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.