VBA AutoFilter how to apply 2 ranges for selection to copy

[Guest]

Using XL 2003 & 97

Currently the code below works fine. It selects values equal to or greater
than 20000 but less than 38999.

Cells.AutoFilter Field:=3, Criteria1:=">=20000", Operator:=xlAnd, _
Criteria2:="<38999"
Range("A1").Select
...... (copy paste routine)

What is the most efficient syntax to convert the above code to handle:
">=20000 and =<35000"
AND
">=35500 and <38999"

TIA

Dennis

 

[Dave Peterson]

I think you've got trouble.

First, autofilter can only have 2 criteria.

And secondly, I don't think any number will be true for all 4 of your values.
(So I'm guessing you meant OR between the two sets of criteria.)

Do you know about Data|Filter|Advanced filter?

You can give it a criteria range and use that to show only the records that
match.

Debra Dalgleish has some notes at:

http://www.contextures.com/xladvfilter02.html

But say your Field3 header was named QTY.

You could put this in an unused portion of your workbook:

QTY QTY

=20000 <=35000
=35000 <38999

That's right 3 rows and 2 columns. That advanced filter criteria range will
treat the stuff on the same row as "AND" and use "OR" for the second row.

Another option that I'd use.

Put in a helper column:

=OR(AND(C2>=20000,C2<=35000),AND(C2>=35000,C2<38999))

And copy down the range of rows.

Then use that in your filter.