VBA – Turn Off AutoFilter / Clear Filters

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on January 6, 2022

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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples