define an Array in Filter as a String

abhay_547

Board Regular
Joined
Sep 12, 2009
Messages
179
Hi,

I want to define an Array as a string and fetch the values for the same from a cell in a autofilter, below is what i have tried so far but it's not working. can someone help

existing code:
Code:
Selection.AutoFilter    

ActiveSheet.Range("$A$1:$U$9995").AutoFilter Field:=5, Criteria1:=Array("Same Day", "Second Class", "Standard Class"), Operator:=xlFilterValues

new code:
Code:
Dim FilterCriteria As String
FilterCriteria = ThisWorkbook.Sheets("Sheet1").Range("J3").Value

Selection.AutoFilter    

ActiveSheet.Range("$A$1:$U$9995").AutoFilter Field:=5, Criteria1:=Array(FilterCriteria), Operator:=xlFilterValues
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,102
How are the values in J3 separated? If, for example, they are separated by comma, try...

Code:
    Dim FilterCriteria As Variant
    FilterCriteria = Split(ThisWorkbook.Sheets("Sheet1").Range("J3").Value, ",")

    ActiveSheet.Range("$A$1:$U$9995").AutoFilter Field:=5, Criteria1:=FilterCriteria, Operator:=xlFilterValues
Change the delimiter accordingly.

Hope this helps!
 
Last edited:

abhay_547

Board Regular
Joined
Sep 12, 2009
Messages
179
How are the values in J3 separated? If, for example, they are separated by comma, try...

Code:
    Dim FilterCriteria As Variant
    FilterCriteria = Split(ThisWorkbook.Sheets("Sheet1").Range("J3").Value, ",")

    ActiveSheet.Range("$A$1:$U$9995").AutoFilter Field:=5, Criteria1:=FilterCriteria, Operator:=xlFilterValues
Change the delimiter accordingly.

Hope this helps!

Is there a way I can run this in loop for a range of cells where I have the Sheet name (in which the data needs to be pasted post filtering) in Column A and the Criteria in Column B (separated by Commas).
 

Watch MrExcel Video

Forum statistics

Threads
1,090,217
Messages
5,413,130
Members
403,464
Latest member
TheRepairGuy

This Week's Hot Topics

Top