Solved: More than 2 criteria - possible??

[joelle]

Hello Experts,
I dont know if it is all possible to set custom autofilter that does more than 2 criteria. I have one piece of code below which part of a much longer module and I try to filter col A with 3 criteria like:
If cell value is "greater" than zero -- criteria 1
or
cell value is "equal" to ">" -- criteria 2 (looking at caret sign)
of
cell value is "equal" to "&" -- criteria 3 (looking at amper sign)

vba is:
[VBA] Range("A9").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlOr, Criteria2:="=>"[/VBA]

Is this possible *without* using lookup. Cant afford lookup table in my actual very long module.
Many thanks.
J.

[geekgirlau]

AFAIK you can't have more than the 2 criteria, but a way around this is to create a reference field in your data that evaluates the 3 options, and gives you a single value that you can then filter on.

For example, your formula might be =OR(A2>0,A2=">",A2="&"). This will create a value of either TRUE or FALSE, which you can then filter.

[Bob Phillips]

Just add a filter to all the columns then run a 2 criteria filter, then a further filter.

[joelle]

Hello,
Thank you both for the tip. I read but have never tried this kinda "advanced filter" and I try to avoid using a 2nd column for my vba, but I will explore this further. If it possible, pls would you attach a simple spreadsheet of how the true-false option works for more than 2 condition filtering.
Many thanks.
J.

[lucas]

Attached is an example of a workaround to filtering by mult criteria...it copies to another worksheet.

[joelle]

Hello Steve,
Thank you very much for the sample spreadsheet and the codes.
So I learn some advanced excel today. Thats very nice of you.
J.

[lucas]

Hi J,
Not so advanced but very useful....post back if you run into problems....even after marking your thread solved.

[joelle]

post back if you run into problems....even after marking your thread solved.

Steve - you read my mind .. I like what you said above.
Anyhow, I do not run into problems but I'm not savvy enough to apply the tips to my simple case. I definitely save your tips for my future endeavour.
Presently, I try to understand and be able to do excel feature "advanced filter" to filter "one column, 3 conditions". I followed the excel "help" menu but obviously its not helpful, so I attach here a very small spreadsheet with the hope I could get some simple step to get to the filtered list.
Many thanks.
Joelle.

[lucas]

Hi joelle,
I don't think you can autofilter by more than 2 criteria without doing it twice as Bob suggests above but this will look for the blank cells and hide those rows...not sure if it's what your looking for.

[joelle]

Steve,
I just try to do Advanced filter for "one column, multiple criteria" as shown under the Excel Help (F1), as attached, but I cant get there although I followed the steps.
I do not need to use vba to hide any row the cell is blank under "qty".
I need to do filtering, just like what is said under the help page that I attach here. If you do a help search for "advanced filter" from your Excel, you will see the same help instructions that I struggle with.
Thanks.
Joelle

[lucas]

It doesn't seem to find the right arrow so I tried a wildcard charactor *

try it on the attachment....it seems to work

[joelle]

It doesn't seem to find the right arrow so I tried a wildcard charactor *
try it on the attachment....it seems to work

Oh, thats why I struggled for too long just because of the ">" sign !!!
Your spreadsheet works beautifully.
Thank you Thank you Thank you Thank you !

Joelle

[lucas]

Had me going to Joelle......glad we got it hashed.

[joelle]

Hi Steve,

Sorry to become a bit greedy here .. wondering if I *may* ask for some frosting for my cake

Since one of the 3 conditions there is "greater than zero", I put ">0" for the criteria range and it does the filter okay.
In reality however, I also have formula and other off-criteria texts in the qty column, and these get filtered as well.
I know there is some wrong with how I set "greater than zero" .. any ideal how to get rid of this flaw?
As said, this is frosting that I can live without. I feel odd now asking too much after my thread was hastily marked "solved"

Joelle

[lucas]

Absolutly not a problem to ask but I'm not sure what your saying....is it that you have formula's with > in them and they are being caught in the filter?

[joelle]

What I meant was:
criteria 1 - if cell value is > 0 - and I put in >0 for the criteria range
criteria 2 - if cell value is "*" (no problem here)
criteria 3 - if cell value is "&" (no problem here)

I think the function does not like >0 for criteria 1, and it filters everything else in column A, except for real blank cells. What I mean for "real blank" is, there are other blank cells there but they are not blank - they have formula in there and get filtered as well.
Maybe the criteria range (C1,C2,C3) only allows texts?

Joelle

[lucas]

I tried several things and this seems to work Joelle...
I used *> for the criteria....
see attached.

[joelle]

Hello Steve,

It does work very well!
I sincerely appreciate your lasting patience with my quest, your several postbacks, and your precious time.

Many thanks again.
Joelle

[lucas]

Hello Steve,

It does work very well!
I sincerely appreciate your lasting patience with my quest, your several postbacks, and your precious time.

Many thanks again.
Joelle

Your very welcome Joelle....your response is what makes this worth doing...

Hope things have warmed up in La (if that's where your at). We were there for my son's wedding a week ago and we nearly froze...we had packed for a warm California.

[joelle]

Steve,

It does a bit too much than "warmed up"
We have been getting a heat wave and today is just a bit better but Sunday and yesterday were cooking!!
How do you like LA - I'm not an LAer but my bros and sis live there. I find LA hasty and the folks there warm up easily. I live in the Silicon Valley, 1/2 hour from the San Francisco and I love this place. But I've never been to Oklahoma, some time in the future maybe ...

Thanks again Steve and enjoy your week.

Joelle