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.