What is the syntax to clear a filter but leave drop-down icons?

Sheets("<sheet name>").ShowAllData
'examples
Sheets("RevenueSummary").ShowAllData
ActiveSheet.ShowAllData

For another version of clear filter that does remove the filter drop-down icons, click here.

Advertisements

What is the syntax to clear an autofilter?

Sheets("<sheet name>").Range("<chart cell>").AutoFilter
'where chart cell is the right-most, top-most cell containing filtered data
'clears the filter dropdown icons as well
'example
Sheets("RevenueSummary").Range("A1").AutoFilter

For another version of clear filter that doesn’t remove the filter drop-down icons, click here.

 

What is the syntax to autofilter?

Sheets(“<sheet's name>”).Range(“<range of filtered data>”).AutoFilter Field:=<column of data> Criteria1:=<filter value>
‘example
'In worksheet Closet, filters 100 rows in column A for values containing Dresses
Sheets(“Closet”).Range(“A1:A100”).Autofilter Field:=1 Criteria1:=“Dresses”


'example
'to filter a list by sales from "north" region
Sheets("salesdata").Range("B1").AutoFilter field:=3, Criteria1:="North"