VBA - Autofilter Multiple Criteria in single Cell - How to get "," to equal OR

[Ernest_PLM]

Hi all,

I've set up an macro to perform an Autofilter and copy the results to a new WorkSheet. HOWEVER, what i need help with is any code that would allow me identify any "," (comma) within a text string of a single cell to equal an "OR" so that it filters by multiple criteria.

For example

If my Data Source has:
ColumnA
Red
Blue
Green
Purple
Pink

AND

my Criteria cell is:
Red, Green, Pink

How do i get VBA to recognise that i want to return these 3 values from the data set?

I know that it would be easier to separate these 3 values into 3 different cells or to hard code it into the macro but it's not an option in my case and really need to get the code to recognise "," = OR.

Here is my VBA currently:

Sub a()

Sheets("Criteria").Activate
Sheets("Criteria").Range("H1:O1").Select
Selection.Copy
Sheets("Output").Select
Range("A1").PasteSpecial
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Range("C4").Select
Sheets("Source").Select
Application.CutCopyMode = False
Sheet1.Range("A3").Select
Sheets("Output").Select
Sheets("Source").Range("A1:H100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("H1:O2"), CopyToRange:=Sheets("Output").Range("A1:H1"), Unique:=False

End Sub

 

[Jon von der Heyden]

Hi

Example using AutoFilter:

Public Sub foo()
    Dim rngCriteria     As Excel.Range
    Dim rngValues       As Excel.Range
    
    Set rngCriteria = Range("D1")
    Set rngValues = Range("A1:A14")
    
    Call rngValues.AutoFilter(Field:=1, Criteria1:=Split(Replace$(rngCriteria.Text, " ", ""), ","), Operator:=xlFilterValues)
    
End Sub

The key thing to note is the Split. I have used the Split function to return an array of the 3 filter values, which can be fed to AutoFilter using xlFilterValues operator. Note, I had to replace space characters because the list appears to be split by comma and space, and I wasn't sure if this would be consistent.