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

13 Comments

  1. Colin says:

    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. Jeff Weir says:

    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. Jeff Weir says:

    So my blogpost subject line sucks, really. Have edited it, and added some more text re what I’m talking about.

  4. Colin says:

    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
  5. Jeff Weir says:

    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.

  6. Colin says:

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

  7. sam says:

    @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

  8. Jeff Weir says:

    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.

  9. Mike Rizza says:

    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!

  10. Jeff Weir says:

    And it’s not even my blog!

  11. Mike Rizza says:

    Genius. Pure genius.

  12. Doug Glancy says:

    Jeff, Jeff, Jeff I already covered this in “Filter as you type with Filtermatic!” Really now.

  13. Doug Glancy says:

    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.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

You must be logged in to post a comment.

Here's how to update your reports of company and nearly any web data: