How to clear filters from all worksheets in active workbook in Excel?

In this article we will learn how to remove the filters applied on various sheets of a workbook. Many users apply filters on the sheets to view the desired data and later save the file as is without removing the filters. This may create confusion for the user while opening the file again, as he might forward the filtered data to someone else for reporting or analysis purpose and the complete details might get missed. Please follow the below steps to perform this activity.

Clear all the filters of a worksheet with a VBA Code

Tip*: The filter of a sheet can be removed using the keyboard shortcut as Alt+D+F+F.

To remove the filters from all sheets of a workbook, follow the below steps.

Step 1: Below is the sample data having two sheets with filters applied on the data.

Step 2: 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.

Sub Clear_fiter() /A function is being called to clear the filters.
    Dim Wks As Worksheet /returns a Sheet collection 
    object that only contains the worksheets  
    For Each Wks In ThisWorkbook.Worksheets 
    / specifying each sheet of a worksheet or workbook
On Error Resume Next / If any error occurs in identifying 
the sheet then move to the next sheet.
If Wks.AutoFilterMode Then / Check if Autofilter is on in a sheet.
Wks.AutoFilterMode = False / Disable the autofilter from the selected sheet.
End If / End if no sheet is next.
Next Wks / Otherwise move to next sheet.
End Sub / end of sub

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: Now, go back to the file and press Alt+F8, the Macro dialog box will open as shown below.

Note: While selecting a file in which changes to be applied please consider the following points:

• ⦁ If selected ‘All Open Workbooks’ it will remove filter from all the open excel files.

• ⦁ If selected ‘This Workbook’ it will remove filter from the active sheet of respective workbook.

• ⦁ If selected ‘’ it will remove filter from all sheets of respective workbook.

Step 8: Now all the filters will be removed.

Conclusion

So this article is all about removing the applied filters from all active workbooks and their sheets. It should be general practice to always remove all the filter from a workbook before saving the file to avoid any data loss during data analysis. There are many other methods to remove filter from individual sheet or all workbooks. More methods will be shared in upcoming articles. Keep learning and Keep exploring the excel application.