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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,108
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,508
Messages
5,414,972
Members
403,558
Latest member
Hardeni

This Week's Hot Topics

Top