Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Quick VBA question: Checking if a filter is applied

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Location
    London
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Quick VBA question: Checking if a filter is applied

    Hi,

    I am using

    ActiveSheet.ShowAllData

    in a piece of VBA and it causes an error if the sheet does not have a filter applied! Is there an if statement I can use to check if the data is filtered?

    Thanks - this site is an absolute life saver!

    Rich

  2. #2
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This should help.
    Code:
    Sub TurnAutoFilterOn()
    'check for filter, turn on if none exists
      If Not ActiveSheet.AutoFilterMode Then
        ActiveSheet.Range("A1").AutoFilter
      End If
    End Sub
    John

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

    Default

    Hi Rich

    You can do this in combination with the AutofilterMode property:

    Code:
    If Activesheet.AutoFiltermode Then 'autofilter is 'on'
       On Error Resume Next   'turn off error reporting
       Activesheet.ShowAllData
       On Error Goto 0   'turn error reporting back on
    End If
    Richard Schollar

    Using xl2013

  4. #4
    New Member
    Join Date
    Jan 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Quick VBA question: Checking if a filter is applied

    the correct property is FilterMode, hence
    Code:
    If Sheets("archive").FilterMode Then Sheets("archive").ShowAllData

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,720
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Quick VBA question: Checking if a filter is applied

    Depends on your definition of "the sheet does not have a filter applied!".

    If as the OP appears to want it only if the data is being filtered then you are correct.
    Last edited by MARK858; Aug 18th, 2018 at 04:02 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

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
  •