Automatically Clear Excel Filters Before Saving a Workbook
Question details
Automatically clear active 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.
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.
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.
In desktop Excel, open the workbook and press Alt+F11. In the Project pane, expand the workbook and double-click ThisWorkbook.
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
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). When prompted, enable macros only if you trust the workbook.
Apply a filter on a worksheet, leave that sheet active, and click Save. Check that the filtered-out rows reappear.

Clear Filters Manually in Excel for the Web
Use this approach when working in a browser, where VBA events cannot run.
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.

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.




