Clear filter from Table

25
votes

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?

4
vbaexcel
show relevant code for more precise help please – MacroMarc
The answer here should work to showalldata in a table.stackoverflow.com/questions/18226045/… – Davesexcel

4 Answers

49
votes

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.

2
votes

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.

1
votes

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:="=*"
-1
votes
ActiveSheet.ShowAllData

Or

Cells.AutoFilter