Macro to check if column is filtered in table and apply filter if not

[kmach60]

Hi everyone,

I'm trying to make a macro that checks column "Sun" on my table "Table1" to see if there is a filtered applied, if there is not I want to apply this filter. I'm also trying to make the macro dynamic by searching column headers to find the right column since my table changes.

This is what I have so far but was hoping someone could lead me in the right direction.

    Dim lo As ListObject
    Dim iCol As Long
    
    Set lo = Sheet1.ListObjects(1)
    iCol = lo.ListColumns("Sun").Index
    lo.Range.AutoFilter Field:=iCol, Criteria1:="<>"

All this macro does so far is apply the filter, how do I add in the checking to see if the filter is applied part? I've tried to search but I've only found answers that don't apply to tables.

Thank you in advance!

 

[kmach60]

Sorry everyone, I actually need the macro to toggle the filter on and off, not just apply filter if not applied. Any help would be appreciated!

 

[CSmith]

Sheet1.AutoFilterMode = True

Sheet1.AutoFilterMode = False

 

[Fluff]

How about

Sub kmach()
   Dim lo As ListObject
   Dim iCol As Long
   
   Set lo = Sheet1.ListObjects(1)
   iCol = lo.ListColumns("Sun").Index
   If lo.AutoFilter.FilterMode Then
     lo.AutoFilter.ShowAllData
   Else
      lo.Range.AutoFilter Field:=iCol, Criteria1:="<>"
   End If
End Sub

 

[CSmith]

Sorry everyone, I actually need the macro to toggle the filter on and off, not just apply filter if not applied. Any help would be appreciated!

Sub ToggleFilter() ' Uncomment one of these and correct as necessary
  'Sheet1.AutoFilterMode = Not Sheet1.AutoFilterMode  'Either by object name reference
  'Sheets("SheetName").AutoFilterMode = Not Sheets("SheetName").AutoFilterMode  'Or by Sheet name reference
End Sub

 

[opicos]

Read this code, I hope can help you:

This code works like this ==> Check if FIlter on specific Column (indicator) is ON, then removes it, else applies it

.