'Clear filter from Table

I'm trying to run a macro that replaces data in a table in Excel, when the data might initially be filtered.

The code should remove the filter first, then run the rest of the code.

I tried the "Autofilter" command, but then the normal filter option on the table's range weren't visible, and I had to manually create the filter option again (not a big deal to me, but other people use this file).

Is there a way to clear the filter WITHOUT removing filters from the table?

vba excel

Solution 1:^[1]

For a Table, you can simply use the ShowAllData method of its Autofilter object:

activesheet.listobjects(1).autofilter.showalldata

Note this won't error even if there is no filter currently applied.

Solution 2:^[2]

Hi Guys use this "Worksheets("Sheet1").ListObjects(1).Range.AutoFilter = False" and please note if already filter applied it will remove filter on the other hand if filter not applied before it will apply the filter.

Solution 3:^[3]

I am finding that the "...ClearAllData" method fails.

Sneaky - not hugely elegant solution - that works by field (so cumbersome if you need to do the whole table), but easy if you just have one field (e.g. field 2 in my example) is to use the wildcard:

ActiveSheet.ListObjects(1).Range.AutoFilter Field:=2, Criteria1:="=*"

Solution 4:^[4]

This expands on the answer from @Rory (the go-to-answer I look up every time I can't remember the syntax). It avoids errors that occur when the table doesn't contain an auto-filter.

The section If Not .AutoFilter Is Nothing checks for the AutoFilter object property of the ListObject (table). If it Is Nothing then that table had it's auto-filter removed.

With Activesheet.Listobjects(1)
    If not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData
End With

Solution 5:^[5]

ActiveSheet.ShowAllData

Cells.AutoFilter

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Rory
Solution 2	Prashant Mallick
Solution 3	AndyH
Solution 4	ChrisB
Solution 5	Alex Hedley

'Clear filter from Table

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]