VBA & Macro Problems

How to Automatically Clear Filters When a Workbook Closes in WPS Office Spreadsheets

Nimra Malik Nov 13, 2026 869 views

Question details

The user needs a way to automatically clear active filters and unhide hidden columns when a shared workbook closes so the next person opens a clean, unfiltered view.

How to Automatically Clear Filters When a Workbook Closes in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: A shared workbook contains filters and hidden columns, and the owner wants the workbook to reset automatically for the next user.
Observed behavior: The workbook keeps the last applied filters and hidden rows or columns after closing because there is no built-in setting to reset the view automatically.

Before you start

Make sure the workbook is saved in a macro-enabled format such as .xlsm or .xls, and enable the Developer tab in WPS Office Spreadsheets. If macros are blocked in your shared storage, create a clean custom view that users can apply manually.

Solution 1Recommended

Use Workbook_BeforeClose VBA to clear filters and unhide columns automatically

Best when you can save a macro-enabled workbook and open it in the WPS Office desktop Spreadsheets app.

WPS Office Spreadsheets supports VBA workbook events. The Workbook_BeforeClose event runs just before the workbook closes, so it clears the current sheet filter, resets hidden columns, and saves the clean state.

Open the VBA editor

Open your workbook in WPS Office Spreadsheets and press Alt+F11 to open the VBA editor.

Select ThisWorkbook

In the Project Explorer pane, double-click ThisWorkbook under your current project.

Paste the BeforeClose code

Paste the following code: Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next ActiveSheet.ShowAllData Columns.Hidden = False ThisWorkbook.Save End Sub

Save as a macro-enabled workbook

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) and allow macros when the security warning appears.

Close and verify

Close the workbook, reopen it, and confirm that filters and hidden columns are cleared automatically when it closes.

Use Workbook_BeforeClose VBA to clear filters and unhide columns automatically

Filters now reset automatically: When macros are enabled, WPS Office Spreadsheets runs the BeforeClose code every time the workbook closes, so the next user opens a clean view.

Solution 2

Clear the previous user's view when the workbook opens with Workbook_Open

Use this method if you prefer to reset filters and hidden columns at the start of each session instead of at close.

Solution 3

Create a manual reset process when macros are unavailable

Useful for Teams, Excel for the web, or any shared environment where VBA macros do not run.

WPS Office solution

Automatically Clear Workbook Filters and Unhide Columns with WPS Office Spreadsheets

Save the workbook as a macro-enabled file in WPS Office Spreadsheets and add a Workbook_BeforeClose macro to reset filters and hidden columns before closing, so every user starts with a clean view.

1. Enable the Developer tab: In WPS Office Spreadsheets, click File > Options > Customize Ribbon, then enable the Developer tab.
2. Open the VBA editor: Press Alt+F11 to open the VBA editor and double-click ThisWorkbook in the Project Explorer.
3. Add the BeforeClose macro: Paste the Workbook_BeforeClose code that calls ShowAllData and sets Columns.Hidden = False.
4. Save as .xlsm: Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the macro is preserved.
5. Test the automatic reset: Close and reopen the workbook with macros enabled to verify that filters and hidden columns are cleared as expected.

Resets filters and hidden columns automatically every time the workbook closesFully compatible with Excel macro-enabled .xlsm filesFree, lightweight WPS Office Spreadsheets with a familiar ribbon interfaceNo Microsoft 365 subscription required

microsoft office alternative - wps office

Frequently Asked Questions

Why don't filters clear automatically when my workbook closes?

There is no built-in Excel or WPS Office setting that clears filters on close. You need a VBA Workbook_BeforeClose macro or a Workbook_Open macro in a macro-enabled workbook.

Does this macro work in Excel for the web or Teams?

No, VBA macros do not run in Excel for the web or Teams. Open the workbook in the WPS Office desktop Spreadsheets app, or use a manual custom view in macro-blocked environments.

Will the macro clear filters on all sheets?

The basic code only clears the active sheet. To reset every sheet, loop through Worksheets and run ws.ShowAllData and set ws.Rows.Hidden = False and ws.Columns.Hidden = False.

How do I enable macros in WPS Office Spreadsheets?

After saving a .xlsm file, click Enable Content when the security warning appears. You can also adjust macro security settings from the Developer tab.

Is a WPS Office macro-enabled file compatible with Microsoft Excel?

Yes, WPS Office saves macro-enabled workbooks as .xlsm, which is compatible with Microsoft Excel. The user must enable macros when opening the file in either application.

Excel & Data Problems