Disable Autofilter Excel VBA – Filter Switch On OFF

1. Check if AutoFilter is On or Off in any Worksheet

This below vba function returns True if autofilter is enabled in a worksheet. otherwise returns false.

Note that we can have only one Autofitler for each worksheet.

Function AutoFilter_Enabled(wSh As Worksheet) As Boolean
    Dim bReturn As Boolean
    
    bReturn = False
    If wSh.AutoFilterMode = True Then
        bReturn = True
    End If
    
    AutoFilter_Enabled = bReturn
End Function

Check if Autofilter is Enabled in Activesheet

Instead of checking for any sheet, if You are looking to verify only for the activesheet, then use the function :- ‘Activesheet.AutoFilterMode = True’ inside a if condition

2. AutoFilter in all Sheets in Workbook

This code will loop thru all the sheets in the workbook. Checks in each sheet if Autofilter is enabled.

Then gives messagebox with the sheet name on Auofilter status.

Sub AutoFilter_Enabled_Check_AllSheets()
    Dim i As Double
    
    For i = 1 To ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(i).AutoFilterMode Then
            MsgBox ThisWorkbook.Sheets(i).Name & " has Autofilter Enabled"
        End If
    Next
    
End Sub

3. Switch Off AutoFilter in a Sheet

The below code will do 2 functions. 1. Check if Autofilter is enabled in current active sheet. If so, disable it.

2. Then loop through each worksheet , check if AutoFilter is on. If so, disable it.

Sub Switch_OFF_AutoFilter()
    Dim wSh As Worksheet
    
    'Switch Off AutoFilter in Current Active Sheet
    If ActiveSheet.AutoFilter Then
        ActiveSheet.ShowAllData
    End If
    
    'Switch Off AutoFilter in All Worksheets
    For Each wSh In ThisWorkbook.Sheets
        If wSh.AutoFilter = True Then wSh.ShowAllData
    Next
End Sub

To know more about filtering values in a Autofitler or fetch filtered list of values, read this article.