Clear multiple filters within the same tab/sheet

[drom]

[]Hi and thanks in advance![/b]
I have a tab/sheet with multiple different tables
some time I have to filter a table and I do forget to clear the filters when moving to one other sheet and saving the Book

Takes me quite lot time and is so tedious to check in which table I have applied filters

I DO KNOW how to Clear remove filters using VBA, but I would like to know if there is any other way rather than going table by table and/or using VBA to CLEAR EVERY FILTER on the existing tables in a sheet


Any idea ?

Thanks again!

 

[MartyS]

Excel allows one autofilter table per worksheet...

If you're currently going to a worksheet, then to the headings, then clearing each heading individually... a quicker way would be:


With the DATA ribbon selected..
Go to the worksheet with the autofilter data... (regardless of whether you know it's filtered or not)
Click on the CLEAR button immediately to the right of the larger FILTER button. This clears all filters for a given table.






Another solution (vba) is:

For each worksheet, add the following code (note WORKSHEET, not MODULE)

Private Sub Worksheet_Deactivate()


    Me.ShowAllData


End Sub

Now, every time you leave a worksheet, it automatically "unfilters" / shows-all the data.

This way, every time you go to a different worksheet, you know it will not be filtered.





If you didn't want it to automatically clear the filter of the sheet you're leaving, you could always just put Me.ShowAllData into a sub in a Module, and bind a shortcut-key to it.

e.g.

Sub CtrlShiftF()
    Me.ShowAllData
End Sub

A final solution might be:

Add this into a module and add a button or bind a shortcut-key to it.

Sub FilterClear()

Dim Wks As Worksheet

For Each Wks In ThisWorkbook.Worksheets
Wks.ShowAllData
Next Wks

End Sub