Is it impossible to AutoFilter based on a range of values?
Results 1 to 6 of 6

Thread: Is it impossible to AutoFilter based on a range of values?

  1. #1
    New Member
    Join Date
    Dec 2017
    Location
    United States
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Is it impossible to AutoFilter based on a range of values?

    Need to AutoFilter based on a range of values, such as:
    Code:
    Worksheets("Sheet2").Range("A1:A5").Value
    When the range code is inserted into the rest of the VBA code, such as:
    Code:
    Worksheets("Sheet1").Range("$A$5:$C$100").AutoFilterField:=2, Criteria1:=Worksheets("Sheet2").Range("A1:A5").Value

    The code filters only for the value in A5. The remaining values in the range are ignored.

    Any ideas on how to AutoFilter based on a range of values?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Is it impossible to AutoFilter based on a range of values?

    How about
    Code:
    Dim ary As Variant
    ary = Worksheets("Sheet2").Range("A1:A5").Value
    Worksheets("Sheet1").Range("$A$5:$C$100").AutoFilter 2, Application.Transpose(ary), xlFilterValues
    - 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
    New Member
    Join Date
    Dec 2017
    Location
    United States
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is it impossible to AutoFilter based on a range of values?

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Dim ary As Variant
    ary = Worksheets("Sheet2").Range("A1:A5").Value
    Worksheets("Sheet1").Range("$A$5:$C$100").AutoFilter 2, Application.Transpose(ary), xlFilterValues
    You're a Godsend. Where did you learn all of this stuff??? Incredible. Thanks, Fluff.
    Last edited by Eric G; Aug 22nd, 2019 at 09:58 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Is it impossible to AutoFilter based on a range of values?

    You're welcome & thanks for the feedback.

    I learnt most of what I know from this wonderful site
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,661
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is it impossible to AutoFilter based on a range of values?

    I don't think so, I think Fluff was born with all that wisdom
    Regards Dante Amor

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Is it impossible to AutoFilter based on a range of values?

    Nice of you to say so, but I consider myself to be an enthusiastic amateur and have learnt a formidable amount (either directly or indirectly) from a lot of the other members here.
    - 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
  •