How do you check for existence of Autofilter in a Table?
Results 1 to 8 of 8

Thread: How do you check for existence of Autofilter in a Table?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default How do you check for existence of Autofilter in a Table?

    If I have an Excel Table, and want to check if Autofilter is applied (to the Table), how do I go about it?

    I have tried:

    Code:
    If Activesheet.ListObjects(1).Autofilter.FilterMode Then ...
    which errors out if Autofilter isn't applied.

    I have also tried:

    Code:
    If Activesheet.AutofilterMode Then ...
    But this returns False if it is a Table.

    I could turn off error reporting to avoid the code bugging out but I assume there is some other way I have missed?

    Thanks for any help
    Richard Schollar

    Using xl2013

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,099
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    Quote Originally Posted by Richard Schollar View Post
    If I have an Excel Table, and want to check if Autofilter is applied (to the Table), how do I go about it?

    I have tried:

    Code:
    If Activesheet.ListObjects(1).Autofilter.FilterMode Then ...
    which errors out if Autofilter isn't applied.


    Just going to sleep for the night, but a quick test shows this code works fine for me (XL2010 on Win8.1)...
    Code:
    Sub Test()
      If ActiveSheet.ListObjects(1).AutoFilter.FilterMode Then
        Debug.Print "Yes"
      Else
        Debug.Print "No"
      End If
    End Sub
    Last edited by Rick Rothstein; Sep 4th, 2014 at 05:41 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    Hi Rick

    That is definitely bugging out for me (Run time error '91': Object variable or With block variable not set).

    I am using Excel 2013 64bit on Windows7.
    Richard Schollar

    Using xl2013

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,737
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    Rich,
    Do you have the filter arrows visible? If not, you'd get the 91 error, so you'd need:

    Code:
    Sub Test()
    If ActiveSheet.ListObjects(1).ShowAutoFilter
      If ActiveSheet.ListObjects(1).AutoFilter.FilterMode Then
        Debug.Print "Yes"
      Else
        Debug.Print "No"
      End If
    Else
       debug.print "No filter available"
    End If
    End Sub

  5. #5
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    Hi Rory

    Perfect - thank you, that was exactly what I needed. Filter arrows weren't visible.
    Richard Schollar

    Using xl2013

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,737
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    No worries. Nice to see you again - been a while!

  7. #7
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    You too - I must arrange an evening pass from the wife for sometime soon
    Richard Schollar

    Using xl2013

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,737
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How do you check for existence of Autofilter in a Table?

    Not too soon - I need to build up some brownie points again first!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •