VBA & Macro Problems

Fix Excel Table Filters Not Working After VBA ShowAllData

Kushani NimanthikaKushani Nimanthika Nov 3, 2026 868 views

Question details

Excel column filter controls become unresponsive after clearing table filters using the VBA AutoFilter.ShowAllData method.

Fix Excel Table Filters Not Working After VBA ShowAllData
Product
Excel
Device & OS
not provided
Scenario
Running a VBA macro to clear table filters.
Observed behavior
The VBA AutoFilter.ShowAllData command successfully clears the filters, but the user cannot manually click or use the column filter drop-down menus afterward.
Before you start

Before modifying your VBA code, verify if the worksheet is currently protected by checking the Review tab, as sheet protection is the most common cause of locked filter controls.

Solution 1Recommended

Adjust Worksheet Protection Settings in VBA

Modify your VBA code to temporarily unprotect the sheet before running ShowAllData, and then reprotect it with AutoFilter permissions enabled.

When a worksheet is protected without explicitly allowing filtering, VBA can still manipulate the data if coded to do so, but the user interface controls (like filter drop-downs) become locked. Adjusting your macro ensures the interface remains interactive.

1
Unprotect the sheet before clearing filters

Add the Worksheet.Unprotect method in your VBA code right before calling the AutoFilter.ShowAllData command. For example: ActiveSheet.Unprotect Password:="YourPassword".

2
Apply ShowAllData

Execute the ShowAllData method to safely clear the existing filter criteria from your table: ActiveSheet.ShowAllData.

3
Reprotect with AutoFilter enabled

Use the Worksheet.Protect method to reapply protection, making sure to set the AllowFiltering argument to True. For example: ActiveSheet.Protect Password:="YourPassword", AllowFiltering:=True.

Adjust Worksheet Protection Settings in VBA
Preventing Errors: To avoid runtime errors, always check if filters are active before clearing them by wrapping your command in an If statement: If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData.

Write and Run VBA Macros Seamlessly in WPS Office

WPS Office offers robust built-in support for VBA macros, allowing you to run, edit, and debug your automated scripts, including AutoFilter and worksheet protection tasks, exactly as you would in Microsoft Excel.

  1. 1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsb file containing the AutoFilter macro.
  2. 2. Access the Developer tools: Navigate to the Developer tab on the top ribbon to access the VBA Editor and Macro security settings.
  3. 3. Edit the VBA code: Click the Visual Basic button to open the editor, locate your specific module, and add the AllowFiltering:=True argument to your sheet protection code.
  4. 4. Run the macro safely: Save your changes and click Run Macro to execute the script. The filters will clear while keeping the drop-down controls fully active for users.
Fully compatible with Microsoft Excel VBA macros (.xlsm, .xlsb)Seamlessly handles worksheet protection and AutoFilter commandsFree and lightweight alternative for powerful spreadsheet automationFamiliar user interface requires zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why does the ShowAllData method cause a runtime error in VBA?

The ShowAllData method will trigger a runtime error if the worksheet has no active filters applied. Always use 'If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData' to verify that filters are active before attempting to clear them.

Can I keep the worksheet protected but still allow users to filter data?

Yes. When applying worksheet protection manually through the Review tab or via VBA, you can enable the 'Use AutoFilter' option (or set AllowFiltering:=True in your VBA code) so users can interact with existing filter controls.

How do I clear filters from a specific ListObject table instead of the whole sheet?

To clear filters for a specific table without affecting other data ranges, reference the table directly using 'ActiveSheet.ListObjects("YourTableName").AutoFilter.ShowAllData' in your macro code.