Fix Excel Table Filters Not Working After VBA ShowAllData
Question details
Excel column filter controls become unresponsive after clearing table filters using the VBA AutoFilter.ShowAllData method.

- 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 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.
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.
Add the Worksheet.Unprotect method in your VBA code right before calling the AutoFilter.ShowAllData command. For example: ActiveSheet.Unprotect Password:="YourPassword".
Execute the ShowAllData method to safely clear the existing filter criteria from your table: ActiveSheet.ShowAllData.
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.

Manually Unprotect the Worksheet
If you prefer not to change the macro, you can manually unprotect the worksheet from the Excel ribbon to instantly restore filter functionality.
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. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsb file containing the AutoFilter macro.
- 2. Access the Developer tools: Navigate to the Developer tab on the top ribbon to access the VBA Editor and Macro security settings.
- 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. 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.

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.




