If filter criteria matched return values if it doesn't move to another filtering criteria

[siddo]

Hi, I have 8 filtering criteria in a single column, I'm trying to build a macro which would put a filter based on each criteria however the issue is if it doesn't find any value in the filter all other value goes blank and sometimes I'm even unable to put the filter using the formula, request if you could help

Range("AF2").Select
ActiveSheet.Cells(1, 1).AutoFilter Field:=6, Criteria1:="*Pre-Imp*", Criteria2:="*Readiness*", Criteria3:="*S12*", Criteria4:="*IS12*", Criteria5:="*TIX*", Criteria6:="*SSA*", Criteria7:="*#SR*", Criteria8:="*#$*",Operator:=xlAnd
Range("A1").CurrentRegion.Select

 

[siddo]

Hi, I have 8 filtering criteria in a single column, I'm trying to build a macro which would put a filter based on each criteria however the issue is if it doesn't find any value in the filter all other value goes blank and sometimes I'm even unable to put the filter using the formula, request if you could help

Range("AF2").Select
ActiveSheet.Cells(1, 1).AutoFilter Field:=6, Criteria1:="*Pre-Imp*", Criteria2:="*Readiness*", Criteria3:="*S12*", Criteria4:="*IS12*", Criteria5:="*TIX*", Criteria6:="*SSA*", Criteria7:="*#SR*", Criteria8:="*#$*",Operator:=xlAnd
Range("A1").CurrentRegion.Select

I can use the Array option but the data which I have does not all the criteria mentioned, sometimes it has 4 criteria's sometimes it has 5, basically it varies

 

[Alex Blakenburg]

You can't use wildcard filtering for more than 2 criteria on the one field using AutoFilter.
You will need to use either a helper column or Advanced Filter.

If you want to use Advanced Filter,

• what is the sheet name and range (including the heading) that contains your data (or if a table the table name) ?
• What is the sheet name and range (including the heading) that contains your list of items to filter on ?
• What is the heading of Column 6 / the filter column ?

An XL2BB of some of your data would also be helpful.

 

[siddo]

That makes sense, thank you Alex

You can't use wildcard filtering for more than 2 criteria on the one field using AutoFilter.
You will need to use either a helper column or Advanced Filter.

If you want to use Advanced Filter,

• what is the sheet name and range (including the heading) that contains your data (or if a table the table name) ?
• What is the sheet name and range (including the heading) that contains your list of items to filter on ?
• What is the heading of Column 6 / the filter column ?

An XL2BB of some of your data would also be helpful.

 

[Alex Blakenburg]

You're welcome. Glad it was of some help.