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

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

  1. Colin

    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 Post author

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

    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
  4. Jeff Weir Post author

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

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

    @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. Jeff Weir Post author

    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. Mike Rizza

    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. Doug Glancy

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

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax