You can use the following syntax in VBA to clear all filters from an Excel sheet:
Sub ClearFilters()
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
End Sub
This particular macro will clear all filters on the sheet that is currently active.
This macro works by using the ShowAllData method to force all rows that are currently hidden to be visible.
If there are not any rows currently being filtered on the active sheet, then nothing will happen when you run this macro.
The following example shows how to use this macro in practice.
Example: Clear All Filters in Excel Using VBA
Suppose we have the following dataset in Excel that contains information about various basketball players:
Now suppose we add a filter to only display the rows where the value in the Team column is equal to Mavs or Nets:
Now suppose that we would like to clear this filter using VBA.
We can create the following macro to do so:
Sub ClearFilters()
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
End Sub
When we run this macro, the filter will automatically be cleared from the sheet:
Notice that all of the rows that were previously hidden are now visible again because we cleared the filter.
Note: You can find the complete documentation for the VBA ShowAllData method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Filter Pivot Tables
VBA: How to Refresh Pivot Tables
VBA: How to Remove Duplicate Values