Clear Excel Table Filters Automatically When a Workbook Opens
Question details
Automatically clear active filters when a shared Excel workbook opens so a VBA data-entry wizard can work with unfiltered data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Several people use a SharePoint workbook containing filtered tables and a VBA data-entry wizard.
- Observed behavior
- Active table filters cause the wizard to insert records into incorrect rows and affect conditional formatting.
Open the workbook in desktop Excel, because Excel for the web cannot run VBA macros. Confirm that users can open the SharePoint file with macros enabled under their organization's security policy.
Clear Table and Worksheet Filters with Workbook_Open
Use this macro to clear active filters on each worksheet when the workbook opens while retaining filter dropdowns.
The macro checks each Excel table separately, then clears any remaining worksheet-level filter. It does not remove the filter buttons.
In desktop Excel, press Alt+F11 or select Developer > Visual Basic. In Project Explorer, find the project for the SharePoint workbook.
Double-click ThisWorkbook and add the following code. If a Workbook_Open procedure already exists, merge the statements into it instead of creating a second one: Private Sub Workbook_Open() Dim ws As Worksheet Dim tbl As ListObject For Each ws In ThisWorkbook.Worksheets For Each tbl In ws.ListObjects If Not tbl.AutoFilter Is Nothing Then If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData End If Next tbl If ws.FilterMode Then ws.ShowAllData Next ws End Sub
Select File > Save As and save the workbook as Excel Macro-Enabled Workbook (.xlsm) in the appropriate SharePoint location.
Apply filters to each table, close the workbook, and reopen it in desktop Excel. Enable macros if prompted, then confirm the filters are cleared and test the data-entry wizard and conditional formatting.

Manage Spreadsheet Filters with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easier transition. Use WPS Spreadsheets to work with and manually adjust filters; it does not fix Excel's Workbook_Open macro behavior.

Frequently Asked Questions
Will the macro remove the filter dropdowns?
No. ShowAllData clears active filter criteria but keeps the dropdowns available. Setting a worksheet's AutoFilterMode to False instead removes worksheet filter arrows rather than simply clearing criteria.
Will Workbook_Open run in Excel for the web?
No. Open the SharePoint workbook in desktop Excel to run VBA. The workbook must be saved in a macro-enabled format, and macros must be permitted.
Why are filters still active after I reopen the workbook?
Check that the code is in ThisWorkbook, the file is saved as .xlsm, and macros were enabled. Also check whether sheet protection or your organization's security settings prevent the macro from running.
What if the workbook already has a Workbook_Open procedure?
In the VBA editor, open ThisWorkbook and add the filter-clearing statements to the existing Workbook_Open procedure. Do not create a second procedure with the same name.




