VBA – Turn Off AutoFilter / Clear Filters

This tutorial will demonstrate how to turn off /clear AutoFilters in VBA.

AutoFilters can be turned on or off using VBA code.

Turn off AutoFilter in the Active Worksheet in VBA

The following code example turns off AutoFilter in the Active Sheet, checking first that it’s not Off already.

Public Sub KillFilter()
  If ActiveSheet.AutoFilterMode Then
     ActiveSheet.AutoFilterMode = False
  End If
End Sub

Turn on AutoFilter in the Active Worksheet in VBA

The following code example turns on AutoFilter in the Active Sheet, checking first that it’s not on already.

Public Sub StartFilter()
  If Not ActiveSheet.AutoFilterMode Then
     ActiveSheet.Range("A1").AutoFilter
  End If
End Sub

Turn off AutoFilter in all Worksheets in VBA.

The following code example loops through each sheet in the entire workbook and turns off AutoFilter in each worksheet, checking first that the filter in the current worksheet is not on already.

Public Sub StopAllFilters()
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
    If ws.AutoFilterMode = True Then
      ws.AutoFilterMode = False
    End If
  Next ws
End Sub

Turn on AutoFilter in all Worksheets in VBA.

Similarly, the following code example loops through the entire workbook and turns on AutoFilter in each sheet, checking first that the filter in the current worksheet is not already on.

Public Sub StartAllFilters()
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
    If Not ws.AutoFilterMode Then
      ws.Range("A1").AutoFilter
    End If
  Next ws
End Sub

Clear All Filters in the Active Worksheet in VBA

The following code example leaves the AutoFilter turned on in the Active Sheet, but clears any filter that are applied to the data.

Public Sub ClearFilter()
  If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData
  End If
End Sub

Clear All Filters in all Worksheets in VBA

Similarly, the following code example loops through the entire workbook and leaves the AutoFilter turned on in each sheet if it is already on, but clears any filter that are applied to the data.

Public Sub ClearAllFilters()
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
    If ws.FilterMode = True Then
      ws.ShowAllData
    End If
  Next ws
End Sub

Clear All Filters in a Table in VBA

Should our worksheet contain a table object, we can adjust the code to just clear any filter that is applied to that filter, while leaving the AutoFilter switched on.

Sub ClearFilterFromTable()
  Dim ws As Worksheet
  Dim sTable As String
  Dim loTable As ListObject
  sTable = "Table1"
  Set ws = ActiveSheet
  Set loTable = ws.ListObjects(sTable)
  loTable.AutoFilter.ShowAllData
End Sub

Should the table object be linked to a Pivot Table, then the Pivot table would refresh accordingly.