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

Ernest_PLM

New Member
Joined
Feb 13, 2013
Messages
2
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
 
Last edited:
Hi

Example using AutoFilter:

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,894
Messages
6,193,541
Members
453,807
Latest member
PKruger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top