VBA Filter

[Danny54]

What would be the best way to filter a worksheet where I need to filter by If "this" or "this" but doesn't contain "this"

below im filtering a table looking for "Se Mo" or "Sc Qu" then wanting to exclude any rows returned that contains 2012.

Didn't think this would be this hard.

Suggestion?


.AutoFilterMode = False
With .Range("A1:H" & LastRow)
.AutoFilter Field:=5, Criteria1:=Array("=*Se Mo*", "=*Sc Qu*", "<>*2012*"), Operator:=xlFilterValues

 

[Fluff]

You can only use more than 2 criteria in an autofilter when filtering on an exact match.
1 option is to put a formula into an empty column that shows which rows should be hidden & filter on that.
Another option would be to use advanced filters.
Failing that you would need to get all the cell values to show & put that into an array, that you could then use to filter.

 

[Danny54]

Thanks Fluff,

i went down the Advanced filter lane last week and found that its not to forgiving if i have blank rows which i do. Before i beat that drum again, can you give me more info on using a inserted column using a formula. I know i can insert a blank column and insert formulas from vba, but lost or inserting a formula. Just what would the formula say... sorry for being so lost.

Thanks

 

[Fluff]

You could use something like
=AND(ISERROR(FIND("2012",E2)),OR(ISNUMBER(SEARCH({"Se Mo","Sc QU"},E2))))

Change Search to Find if you want it case sensitive.

 

[Danny54]

Thanks.

this is an array formula - correct? do i have to do anything special to other than copy and paste into my vba

 

[Fluff]

Not sure if it needs array entry or not, try putting it into the sheet manually & see what happens.

 

[Danny54]

Kewl, not a arrayformula. For the lines containing 2012 it only marks the row false when column E2 starts with a 2012 and nothing else on the line. i could have abc 2012 def in the line and would want it marked false

=AND(ISERROR(FIND("2012",E2)),OR(ISNUMBER(SEARCH({"Se Mo","Sc QU"},E2))))

Thanks

 

[Fluff]

Works for me

+Fluff New.xlsm
	E	F
1
2	se mo	TRUE
3	se mo 2012	FALSE
4	sc qu	TRUE
5	sc qu 2012	FALSE
Lookup

Cell Formulas
Range	Formula
F2:F5	F2	=AND(ISERROR(FIND("2012",E2)),OR(ISNUMBER(SEARCH({"Se Mo","Sc QU"},E2))))

Does it make any difference if you array enter it?

 

[Danny54]

wow, i rekeyed the formula you provided and all is working. Im think i've exhausted office365 today.

Thanks for the help. I would not have thought of using the formula to achieve my needs.

Have a super weekend.

 

[Fluff]

You're welcome & thanks for the feedback