Clearing Excel AutoFilter using VBA

[blafarm]

I am using this VBA code to refresh my entire workbook at specific time intervals.

As you can see, it is currently set to refresh every 60 minutes.

Public RunWhen As Double
Public Const cRunIntervalMinutes = 60
Public Const cRunWhat = "Workbook_RefreshAll"
 
Sub StartTimer()
    RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
         schedule:=True
End Sub
 
Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub
 
Sub Workbook_RefreshAll()
    Application.CalculateFullRebuild
    ActiveWorkbook.RefreshAll
    Call StartTimer
End Sub

The code works great, however there’s one thing I wish it could also do:

1. Any time the sheet is opened, and/or
2. Any time this VBA code executes the sheet refresh process

I would like the AutoFilter to be "Cleared" (not disabled or turned off) as in the screen grab below:

Thanks in advance for any assistance.

Cheers

 

[John_w]

Worksheets("Sheet Name").AutoFilter.ShowAllData

Or to prevent an error if autofilter is turned off:

With Worksheets("Sheet Name")
    If .AutoFilterMode Then .AutoFilter.ShowAllData
End With

 

[Taul]

Hi,
You can check the state of the Autofilter and decide what to do

    With ActiveSheet
        If .AutoFilterMode = True And .FilterMode = True Then
            MsgBox "They are visible and in use"
            .ShowAllData
        ElseIf .AutoFilterMode = True Then
            MsgBox "They are visible but not in use"
            'do nothing
        Else
            MsgBox "They are not visible or in use"
            'do nothing
        End If
    End With

 

[blafarm]

Thank you both for your responses.

Unfortunately, I am a complete novice in terms of VBA, so I don't know how to properly integrate something like this into my existing VBA code:

With Worksheets("Sheet Name")
    If .AutoFilterMode Then .AutoFilter.ShowAllData
End With

But the other issue is that I would also like the sheet to open with with the AutoFilter cleared -- regardless of the state in which it was last saved and closed.

That leads me to believe that I need two statements -- one to clear AutoFilter 'on open' -- and one to clear AutoFilter every time the sheet is refreshed by the VBA code in my first post.

I would be very grateful if some kind soul would insert those two statements into the correct locations of my existing VBA -- so that I don't screw it up.

Thanks very much in advance.

 

[Michael M]

To clear filters on open try

Sub Workbook_open()
With Worksheets("Sheet Name")
    If .AutoFilterMode Then .AutoFilter.ShowAllData
End With
end sub

 

[blafarm]

Thank you all.

Question:

If I only wanted to REAPPLY the filter when the Workbook was first opened -- or when it was refreshed via the VBA in my first post - how might that change the code?

Thanks