Turn Off Autofilter From Code - VBA Code Examples - Automate Excel

In this Article

• Turn off AutoFilter in the Active Worksheet in VBA
• Turn on AutoFilter in the Active Worksheet in VBA
• Turn off AutoFilter in all Worksheets in VBA.
• Turn on AutoFilter in all Worksheets in VBA.
• Clear All Filters in the Active Worksheet in VBA
• Clear All Filters in all Worksheets in VBA
• Clear All Filters in a Table in VBA
• VBA Coding Made Easy

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!

Learn More!