Results 1 to 4 of 4

Thread: VBA Autofilter for 3 or more criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    586
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Autofilter for 3 or more criteria

    I've read several posts about how to use autofilter with more than 2 criteria and the answer seems to be to use an array - I am trying the following but get a Type Mismatch error on the Criteria 2 line?

    Code:
    Criteria1 = "*Meeting 1*"            
    Criteria2 = Array("<>*at their Stand*", "<>*Table (TBC)*")
    For further clarity the criteria are then used laterin the code as follows:
    Code:
    Sheet1.Range("A1").AutoFilter Field:=SponsorColID, Criteria1:=Criteria1, Criteria2:=Criteria2
    Last edited by StuLux; Sep 5th, 2019 at 06:22 AM. Reason: to add clarity
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Autofilter for 3 or more criteria

    You can only use more than 2 criteria if you are doing an exact match.
    For partial matches or <> you are limited to two criteria.
    One option is to use a helper column with a formula that returns true/false & then filter on that.
    And another option would be to have a look at using advanced filters instead.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    586
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autofilter for 3 or more criteria

    Quote Originally Posted by Fluff View Post
    You can only use more than 2 criteria if you are doing an exact match.
    For partial matches or <> you are limited to two criteria.
    One option is to use a helper column with a formula that returns true/false & then filter on that.
    And another option would be to have a look at using advanced filters instead.

    Thank you Fluff - thought I was going mad but you've explained the issue - will investigate advanced filters but will use a helper column for a quick fix, thanks.
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Autofilter for 3 or more criteria

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •