How do you know if a ListObject has the autofilter applied?

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:

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

14 thoughts on “How do you know if a ListObject has the autofilter applied?

  1. 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

  2. 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.

  3. 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"
    Debug.Print "it was there"
    End If

  4. 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.

  5. 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).

  6. @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

  7. Hi Sam. Yes, much better.
    In fact, this will do it:

    Function Chk_LstFilter(lo As ListObject)
    Chk_LstFilter = lo.ShowAutoFilter
    End Function

    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.

  8. 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!

  9. 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.

  10. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.