If you try to filter a ListObject, and someone has turned the entire filter off by deselecting the Filter icon from the Data tab, then you’ll get an error. But how the heck can you easily test if the filter is on or not?
If you fire up the macro recorder, and click the Filter icon a few times to toggle it on and off, then you just get this:
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter
You can write If Selection.AutoFilter = TRUE then... but this simply serves to toggle the autofilter from it’s current state, and always returns true.
It seems to me that the only thing you can do is something like this:
Function FilterIsOn(lo As ListObject) As Boolean
Dim bOn As Boolean
bOn = False
On Error Resume Next
If lo.AutoFilter.Filters.Count > 0 Then
If Err.Number = 0 Then bOn = True
End If
On Error GoTo 0
FilterIsOn = bOn
End Function
Hi Jeff,
The ListObject.Autofilter property returns an Autofilter object. The Autofilter object has a FilterMode property you can use to see if a filter is being used.
Sub foo()
Dim lo As ListObject
Dim af As AutoFilter
Set lo = Sheet1.ListObjects(1)
Debug.Print lo.AutoFilter.FilterMode
End Sub
Hi Colin. Different subject. I’m talking about if someone has turned the filter functionality for the whole table off, by clicking the DATA tab and then deselecting the Filter icon, not whether they have filtered a particular column.
So my blogpost subject line sucks, really. Have edited it, and added some more text re what I’m talking about.
Hi Jeff,
Oh okay – for that I think you can check if the Autofilter object is Nothing. Something like:
Dim lo As ListObject
Dim af As AutoFilter
Set lo = Sheet1.ListObjects(1)
Set af = lo.AutoFilter
If af Is Nothing Then
Debug.Print "it wasn't there"
Else
Debug.Print "it was there"
End If
Much better. Glad I thought of it. :-) Thanks Colin.
It’s kinda weird that Debug.Print lo.Range.AutoFilter would toggle the autofilter on and off, though.
It’s a bit confusing but that line of code would call the Range.Autofilter() method. Being a method (more specifically, a function which returns a Variant), it performs an action when you call it. It’s a different beast to the ListObject.Autofilter property which returns an AutoFilter object.
Right, bedtime for me otherwise I’m going to be cobbling together some pretty horrendous code at work tomorrow (err… today).
@Jeff
Function Chk_LstFilter()
Dim LstObj As ListObject
Set LstObj = Sheet1.ListObjects(1)
Chk_LstFilter = LstObj.ShowAutoFilter
End Function
Returns True if the List Object has Filter dropdowns and false otherwise
Hi Sam. Yes, much better.
In fact, this will do it:
Or even this:
If lo.ShowAutoFilter then …
As per Colin’s comment, I was just confused on account of thinking a method was a property. Makes me realize how new I am at this VBA caper.
You know you are cool in the world of Excel when you don’t post your question to a forum. You just post it to your blog and experts answer it. Props to you Jeff!
And it’s not even my blog!
Genius. Pure genius.
Jeff, Jeff, Jeff I already covered this in “Filter as you type with Filtermatic!” Really now.
Well, maybe “alluded to,” or “mentioned in passing” is more accurate. Or “figured it out once, but had to re-read my post.”
Anyways, cheers.
Hello guys,
Stumbled on this page.
I’m working on excel but having a challenge. I’d like to know if it’s possible to link two spreadsheets such that when I filter on the primary sheet it affects the secondary.
Both sheets contain the same data. It’s just that primary sheet is for input and update of data while the secondary just presents data from the primary sheet but neatly.