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.

Clear filters from all worksheets in active workbook with VBA code

Clear filters from all worksheets in active workbook with VBA code

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.

Related articles:

• 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?