Excel – Unfilter all sheets

There are times when you are using an Excel workbook and you simply want to search for some content but on one of the tables on one of the sheets the table which contains the data has been filtered. You can spend more time looking for and then removing the filter than you do in running the actual search.

This macro will remove all filters from your current workbook.

Sub UnfilterAll()
    For Each WSheet In ActiveWorkbook.Worksheets
        If WSheet.AutoFilterMode Then
            If WSheet.FilterMode Then
                WSheet.ShowAllData
            End If
        End If
        For Each DTable In WSheet.ListObjects
            If DTable.ShowAutoFilter Then
                DTable.Range.AutoFilter
                DTable.Range.AutoFilter
            End If
        Next DTable
    Next WSheet
End Sub

It’s worth noting that excel uses two methods for filtering: the “old” auto-filter way which only allows a single filtered range per sheet and the “new” table based filter.

This macro caters for both methods.

NOTE: It will not restore any filters, if you want that, save the sheet prior to running the macro and then restore this saved version afterwards.