CODEWITHSUNDEEP
excelvbafilter
Waimea
Waimea

Reputation: 71

Excel VBA Reset multiple advanced filters with ShowAllData

I am using 3 advanced filters in a worksheet and I want to reset all 3 tables. Using showAllData only resets the last table. I have tried unhiding the filtered rows without success. Perhaps I should loop through all 3 tables?

Sub ClearFilter()
    ActiveSheet.ShowAllData
End Sub

Upvotes: 0

Views: 4274

Answers (2)

Rey Juna
Rey Juna

Reputation: 347

I think this is what you are looking for:

IF Sheets("Sheet1").ListObjects("Table1").AutoFilter.FilterMode = True THEN Sheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllData

Substitute the sheet and table names you have. You need the IF statment to check if there are filters set, otherwise it will error when you try to clear filters that are not there.

For further reading, see: Why does ActiveSheet.FilterMode returns False when sheet has filter?

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

After ShowAllData clears the filters from the last table, you can unhide the remaining hidden rows to show all the data like this...

Sub ClearFilter()
    On Error Resume Next
    ActiveSheet.ShowAllData
    ActiveSheet.Rows.Hidden = False
End Sub

Upvotes: 2

Related Questions