Multiple criteria filtering

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,148
Column A contains these values:

Rich (BB code):
Name
a
b
c
d
e
I want to filter some values, so use this:

Rich (BB code):
Range("A1).AutoFilter Field:=1, Criteria1:=Array("a", "b"), Operator:=xlFilterValues
which indeed does return the values a and b only.

Question 1.

Why does this return no values?

Rich (BB code):
Dim MyString As String

MyString = "a""" & ", " & """b"

Range("A1).AutoFilter Field:=1, Criteria1:=Array(MyString), Operator:=xlFilterValues


Question 2.

In reality, I want to filter on a number of criteria, eg: "a", "b", "c", ..... , "z", "aa", etc. so need to create an array (or possibly some other method).

Thanks
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,598
Office Version
365
Platform
Windows
Try either of these
Code:
Dim ary As Variant

ary = Array("a", "b")
Range("A1").AutoFilter Field:=1, Criteria1:=ary, Operator:=xlFilterValues


Dim MyString As String

MyString = "a,b"
Range("A1").AutoFilter Field:=1, Criteria1:=Array(Split(MyString, ",")), Operator:=xlFilterValues
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,598
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,095,430
Messages
5,444,408
Members
405,280
Latest member
Galliganr0

This Week's Hot Topics

Top