CODEWITHSUNDEEP
excelvbafilteringautofilter
Christopher Harris
Christopher Harris

Reputation: 21

VBA Excel - If statement for checking table has a filter on it

I am looking for code that will check to see if the table has a filter on it. If it has a filter I want to make sure that it's not filtered and show all data, if it doesn't have a filter then I want to put a filter on it. If the table has a filter and is not filtered then I don't want it to do anything.

Here is what I have so far:

If Sheets("Sheet1").FilterMode Then
   Sheets("Sheet1").ShowAllData
End If

Any help please

Upvotes: 1

Views: 4299

Answers (2)

VBasic2008
VBasic2008

Reputation: 54797

Initialize Filter

Sub InitializeFilter()
    With Sheet1 ' or e.g. With ThisWorkbook.Worksheets("Sheet1")
        If .AutoFilterMode Then
            If .FilterMode Then
                .ShowAllData
                MsgBox "Was previously filtered. Now: 'ShowAllData'."
            Else
                MsgBox "'ShowAllData': do nothing."
            End If
        Else
            .Range("A1").CurrentRegion.AutoFilter
            MsgBox "Applying 'AutoFilter' to ""A1""'s 'CurrentRegion' " _
                & "('ShowAllData')."
        End If
    End With
End Sub

Upvotes: 0

Elio Fernandes
Elio Fernandes

Reputation: 1420

If you are talking about a table object, may be this might help

Sub myTableFilter()
    Dim ws As Worksheet
    Dim ol As ListObject
    
    Set ws = ActiveSheet
    Set ol = ws.ListObjects(1)
   
    On Error Resume Next
    If ol.AutoFilter.FilterMode Then ol.AutoFilter.ShowAllData
    'ol.ShowAutoFilterDropDown = True
    ol.ShowAutoFilter = True
    On Error GoTo 0
End Sub

Take a look at: https://learn.microsoft.com/en-us/office/vba/api/excel.autofilter

Upvotes: 1

Related Questions