VBA & Macro Problems

Clear Excel Table Filters Automatically When a Workbook Opens

Emma BrownEmma Brown Nov 22, 2026 868 views

Question details

Automatically clear active filters when a shared Excel workbook opens so a VBA data-entry wizard can work with unfiltered data.

Clear Excel Table Filters Automatically When a Workbook Opens
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, press Alt+F11 or select Developer > Visual Basic. In Project Explorer, find the project for the SharePoint workbook.

2
Add the open event

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

3
Save as a macro-enabled workbook

Select File > Save As and save the workbook as Excel Macro-Enabled Workbook (.xlsm) in the appropriate SharePoint location.

4
Reopen and test

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.

Clear Table and Worksheet Filters with Workbook_Open
Check permissions and protection: Protected sheets or macro security settings can prevent the code from running or clearing filters. Ensure users have the necessary permissions and follow your organization's security policy.
Free Microsoft Office alternative

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.

Filter and review spreadsheet data in WPS SpreadsheetsOpen and edit Microsoft Excel .xlsx and .xls filesUse a familiar interface for a smoother move from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

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.