VBA & Macro Problems

Automatically Clear Excel Filters Before Saving a Workbook

Adam DavisAdam Davis Nov 18, 2026 870 views

Question details

Automatically clear active Excel filters before saving a workbook.

Automatically Clear Excel Filters Before Saving a Workbook
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to save a workbook with filtered rows visible, including when working in desktop Excel or Excel for the web.
Observed behavior
The desired result is for active filters to be cleared before the workbook is saved.
Before you start

Open the workbook in desktop Excel with VBA support and check which worksheet should have its filters cleared. Use a test copy and enable macros only if you trust the file.

Solution 1Recommended

Clear the Active Sheet's Filter with a BeforeSave Macro

Use a workbook event to clear the active worksheet's filter whenever desktop Excel saves the file.

This macro acts only on the active sheet; it does not clear filters on every worksheet. ShowAllData reveals filtered-out rows but does not remove the filter drop-down buttons.

1
Open the VBA editor

In desktop Excel, open the workbook and press Alt+F11. In the Project pane, expand the workbook and double-click ThisWorkbook.

2
Add the save event

Paste this code into the ThisWorkbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error Resume Next ActiveSheet.ShowAllData On Error GoTo 0 End Sub

3
Save as a macro-enabled workbook

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). When prompted, enable macros only if you trust the workbook.

4
Test the event

Apply a filter on a worksheet, leave that sheet active, and click Save. Check that the filtered-out rows reappear.

Clear the Active Sheet's Filter with a BeforeSave Macro
Scope and limitations: The error handler prevents a save interruption if ShowAllData cannot run, such as when no filter is active. Because it suppresses errors, verify the result on each sheet you intend to use.
Free Microsoft Office alternative

Manage Spreadsheet Filters with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing spreadsheets. It supports common Excel formats for an easier move between apps, although it does not fix Excel's VBA save event; test any macro-dependent workflow separately.

Filter spreadsheet data and clear filters manuallyCompatible with Microsoft Excel XLSX and XLS filesFamiliar spreadsheet controls make everyday editing easierFree and lightweight for routine office tasks
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro clear filters on every worksheet?

No. ActiveSheet.ShowAllData acts on the active sheet when you save. Check other worksheets separately if they also contain filters.

Can Excel for the web run the BeforeSave macro?

No. Excel for the web cannot run VBA macros. Open the workbook in desktop Excel with VBA support to use the event, or clear filters manually from the Data tab in the browser.

Will ShowAllData remove the filter buttons?

No. ShowAllData clears the active filtering so filtered-out rows are visible; the filter drop-down buttons remain available.

Why should I save the workbook as an XLSM file?

The XLSM format preserves VBA code. In desktop Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm).

What if the filter is still active after I save?

Confirm that the filtered worksheet was active, macros were enabled for the trusted file, and the code is in ThisWorkbook. The error handler suppresses errors, so test the filter and save action again on a copy.