define an Array in Filter as a String

[abhay_547]

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:

Selection.AutoFilter    

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

new 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

 

[Domenic]

How are the values in J3 separated? If, for example, they are separated by comma, try...

    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!

 

[abhay_547]

Its working perfectly. Thanks a lot

 

[abhay_547]

How are the values in J3 separated? If, for example, they are separated by comma, try...

    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).