Table of Contents
To clear filters in Excel using VBA, a macro code needs to be written to loop through all the filters in the worksheet and clear them. An example of this code is as follows: For Each filt In ActiveSheet.AutoFilter.Filters filt.On = False Next filt This code will loop through all the filters in the active sheet and set their value to false, thus clearing the filters.
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.