How to clear filters from all worksheets in active workbook in Excel?
Supposing you have created multiple filters across different worksheets in an Excel workbook, and now you want to clear all these filters at once. Normally, you need to shift to the worksheet one by one to check the filter and then clear it manually. Is there any convenient method to clear filters from all worksheets in an active workbook? Please try the method in this article.
Please run the below VBA script to clear filters from all worksheets in active workbook.
1. In the workbook you need to clear filters from, please press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA script into the Module window. See below screenshot:
VBA code: Clear filters from all worksheets in active workbook
Sub Clear_fiter()() 'Updated by Extendoffice 20210625 Dim xAF As AutoFilter Dim xFs As Filters Dim xLos As ListObjects Dim xLo As ListObject Dim xRg As Range Dim xWs As Worksheet Dim xIntC, xF1, xF2, xCount As Integer Application.ScreenUpdating = False On Error Resume Next For Each xWs In Application.Worksheets xWs.ShowAllData Set xLos = xWs.ListObjects xCount = xLos.Count For xF1 = 1 To xCount Set xLo = xLos.Item(xF1) Set xRg = xLo.Range xIntC = xRg.Columns.Count For xF2 = 1 To xIntC xLo.Range.AutoFilter Field:=xF2 Next Next Next Application.ScreenUpdating = True End Sub
3. Press the F5 key to run the code. Then all filters across all worksheets in current workbook are cleared immediately.
- How to clear filter cache (old items) from Pivot Table in Excel?
- How to clear filters when opening, saving or closing workbook in Excel?
- How to filter data based on checkbox in Excel?
- How to fill series of numbers in a filtered list column in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!