How to Clear Filters in Excel Using VBA (With Example)

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.

The following tutorials explain how to perform other common tasks in VBA: