How to clear filters when opening, saving or closing workbook in Excel?

In this article we will learn to remove filters while opening/closing or saving a worksheet. Because whenever a filter is applied in the worksheet it gets saved along with the file and remain as is until manually removed. They filters may be misinterpreted as missing data in the worksheet. The filters can be removed automatically after applying some Visual Basic codes in the respective worksheet. The following are the VBA codes to be incorporated for this task.

• Clear filters while opening a workbook

• Clear filters while saving a workbook

• Clear filters while closing a workbook

Clear filters while opening a workbook

Step 1− We have taken the sample workbook where few filters have been applied on the data.

Step 2− Now press Alt+F11 keys from the keyboard and the Microsoft Visual Basic for Applications window will open.

The above editor can also be opened using the Developer’s tab as shown below −

Step 3− In the Microsoft Visual Basic for Applications window, double click ThisWorkbook available in the Project panel.

Step 4− Now copy the below VBA code and enter the same in the ThisWorkbook (Code) window.

Private Sub Workbook_Open() / sets the scope so that subs in outside modules cannot call that particular subroutine also an event when a workbook is opened.

Dim ws As Worksheet / returns a Sheets collection object that only contains the worksheets

For Each ws In Worksheets / specifying each sheet of a worksheet or workbook

If ws.AutoFilterMode Then / condition to check if any filter has been applied in any worksheet

ws.AutoFilterMode = False / Then set the auto filter mode to false and remove all the applied filters.

End If / If the above condition is false then move to the next one.

Next ws / check the next worksheet.

End Sub / end of subs.

Step 5− Once the code is entered, press Alt+Q keys in the Keyboard to close the Microsoft Visual Basic for Applications window.

Step 6− Next, save the file in the format as, Excel Macro-Enabled Workbook.

Step 7− From next time onwards whenever you will open the workbook the filter will be removed automatically.

Clear filters while saving a workbook

Step 1− This feature is used when you want to remove the filters while saving the worksheet.

Step 2− Follow the steps 2 and 3 of Clear filters while opening a workbook to open the Microsoft Visual Basic for Applications window.

Step 3− Paste the following code in ThisWorkbook

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) /sets the scope so that subs in outside modules cannot call that particular subroutine also an event when a workbook is saved.

Dim ws As Worksheet / returns a Sheets collection object that only contains the worksheets

For Each ws In Worksheets / specifying each sheet of a worksheet or workbook

If ws.AutoFilterMode Then / condition to check if any filter has been applied in any worksheet

ws.AutoFilterMode = False / Then set the auto filter mode to false and remove all the applied filters.

End If / If the above condition is false then move to the next one.

Next ws / check the next worksheet.

End Sub / end of subs.

Step 4− Now again follow the steps 5 and 6 of Clear filters while opening a workbook to save the file. As soon as the file is saved, the filters will be removed and will not be available whenever you open the file.

Clear filters while closing a workbook

Step 1− This feature is used when you want to remove the filters while closing the worksheet.

Step 2− Follow the steps 2 and 3 of Clear filters while opening a workbook to open the Microsoft Visual Basic for Applications window. And paste the following code in ThisWorkbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean) /sets the scope so that subs in outside modules cannot call that particular subroutine also an event when a workbook is closed.

Dim ws As Worksheet / returns a Sheets collection object that only contains the worksheets

For Each ws In Worksheets / specifying each sheet of a worksheet or workbook

If ws.AutoFilterMode Then / condition to check if any filter has been applied in any worksheet

ws.AutoFilterMode = False / Then set the auto filter mode to false and remove all the applied filters.

End If / If the above condition is false then move to the next one.

Next ws / check the next worksheet.

End Sub / end of subs.

Step 3− Now again follow the steps 5 and 6 of Clear filters while opening a workbook to save the file. As soon as the file is closed, the filters will be removed and will not be available whenever you close the file.

Conclusion

Finally, any of the above 3 codes can be entered at a time in the VBA editor to apply the changes in a workbook. These are the most useful and reliable methods to remove all kinds of filters from a workbook. Hope this will help you to resolve your problem. Keep learning keep exploring excel.