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.FilterModereports whether the worksheet is currently showing a filtered result.Worksheet.ShowAllDataclears active filter criteria and displays all rows in the filtered list or lists on that worksheet.Worksheet.AutoFilterModereports whether worksheet AutoFilter arrows are displayed. Setting it toFalseremoves those arrows.ListObject.ShowAutoFiltercontrols 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
ShowAutoFilterproperty toFalse. - 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
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Set Statement: How to Assign Object ReferencesIn VBA, Set is a statement—not a function—that assigns an object reference to an object variable. Use it…
- VBA Dictionary Multiple Items per Key: Practical GuideYes. A VBA Scripting.Dictionary can hold multiple items for one key if you store a container—most often a…
- Excel VBA Error Handler: How to Catch and Fix ErrorsAn Excel VBA error handler is code that tells a macro what to do when a runtime error…
- Excel UserForm ComboBox: Load a List with VBATo load an Excel UserForm ComboBox from worksheet values, add a ComboBox to the form, then fill it…
- Macro Recorder Setup in Excel: A Step-by-Step GuideTo set up the Macro Recorder in Excel for Microsoft 365 on Windows, show the Developer tab, choose…
- Excel VBA Rows: Select, Count, and Manage Worksheet RowsIn Excel VBA, Rows refers to worksheet rows or the rows within a specified range. For example, Worksheets("Data").Rows(5)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.