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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,139
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,095,783
Messages
5,446,474
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top