Clearing Excel AutoFilter using VBA

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
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.

Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,954
Code:
Worksheets("Sheet Name").AutoFilter.ShowAllData
Or to prevent an error if autofilter is turned off:
Code:
With Worksheets("Sheet Name")
    If .AutoFilterMode Then .AutoFilter.ShowAllData
End With
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
644
Hi,
You can check the state of the Autofilter and decide what to do

Code:
    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

Board Regular
Joined
Oct 14, 2018
Messages
59
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:

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.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,975
Office Version
2013
Platform
Windows
To clear filters on open try
Code:
Sub Workbook_open()
With Worksheets("Sheet Name")
    If .AutoFilterMode Then .AutoFilter.ShowAllData
End With
end sub
 

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
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
 

Forum statistics

Threads
1,078,354
Messages
5,339,723
Members
399,320
Latest member
sut3k

Some videos you may like

This Week's Hot Topics

Top