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

Eric G

New Member
Joined
Dec 21, 2017
Messages
35
Need to AutoFilter based on a range of values, such as:
Code:
[LEFT][COLOR=#000000][FONT=Calibri]Worksheets("Sheet2").Range("A1:A5").Value[/FONT][/COLOR][/LEFT]
When the range code is inserted into the rest of the VBA code, such as:
Code:
[COLOR=#222222][FONT=Calibri][SIZE=3][COLOR=#000000]Worksheets("Sheet1").Range("$A$5:$C$100").AutoFilterField:=2, Criteria1:=Worksheets("Sheet2").Range("A1:A5").Value[/COLOR][/SIZE][/FONT][/COLOR]

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?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,501
Office Version
365
Platform
Windows
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
 

Eric G

New Member
Joined
Dec 21, 2017
Messages
35
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,501
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.

I learnt most of what I know from this wonderful site :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,119
Office Version
2007
Platform
Windows
I don't think so, I think Fluff was born with all that wisdom :pray:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,501
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,101
Messages
5,484,672
Members
407,460
Latest member
Fakxi

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top