VBA Filtering with multiple criteria

[Jvalades]

I am trying to create a filter that will select 4 different parts.

another constraint is: Sometimes a part will be added with a similar name so I cannot specify the exact name.

So what I started with is:

ActiveSheet.Range("$A$1:$N$6000").AutoFilter Field:=4, Criteria1:="=*A717*", _
Operator:=xlOr, Criteria2:="=*XBEA*"

Can I add more criteria to this formula. Right now this works for finding the two parts.

Best

 

[L. Howard]

I found this example where you replace the OR operator with AND...

myRange.AutoFilter Field:=1, Criteria1:="<>A", Operator:=xlAnd, Criteria2:="<>B", Operator:=xlAnd, Criteria3:="<>C"

Put your criteria in place of the ="<>A", and others.

Howard

 

[Jvalades]

I found this example where you replace the OR operator with AND...

myRange.AutoFilter Field:=1, Criteria1:="<>A", Operator:=xlAnd, Criteria2:="<>B", Operator:=xlAnd, Criteria3:="<>C"

Put your criteria in place of the ="<>A", and others.

Howard

Howard,

Thank you for the reply. Unfortunately this is not working for me. When substituting the OR operator with AND, the product is looking for a combination of the two filters. Linking them. Meaning I would have to have a part with both criteria in one cell. Instead of the separated search.

JV

 

[L. Howard]

Howard,

Thank you for the reply. Unfortunately this is not working for me. When substituting the OR operator with AND, the product is looking for a combination of the two filters. Linking them. Meaning I would have to have a part with both criteria in one cell. Instead of the separated search.

JV

From a Teach Excel site, try something like this, where you change the range and of course the criteria and add additional lines for each criteria.

I believe you can also alter the Fields to filter in the order that suits your filter needs, that is... Field:=6, Field:=1, Field:=9, Field:=3, etc.

Howard

Sub AutoFilter_in_Excel_Multiple_Col_Filter()

Range("A1").AutoFilter Field:=1, Criteria1:="Enter Criteria Here"

Range("A1").AutoFilter Field:=2, Criteria1:="Enter Criteria Here"

Range("A1").AutoFilter Field:=3, Criteria1:="Enter Criteria Here"

End Sub

 

[Marcelo Branco]

I am trying to create a filter that will select 4 different parts.

another constraint is: Sometimes a part will be added with a similar name so I cannot specify the exact name.

So what I started with is:

ActiveSheet.Range("$A$1:$N$6000").AutoFilter Field:=4, Criteria1:="=*A717*", _
Operator:=xlOr, Criteria2:="=*XBEA*"

Can I add more criteria to this formula. Right now this works for finding the two parts.

Best

You can achieve this with Advanced Filter and using a Formula as criteria.

See the example below with a small data sample

Before filter

Create a named range MyList with the codes of interest (E2:E5 in the example)

	A​	B​	C​	D​	E​
1​	Code​				MyList​
2​	text A717​				A717​
3​	text A718​				XBEA​
4​	A717​				XXXX​
5​	tyio​				XYZW​
6​	XBEA bla​
7​	A717 text​
8​	A718​
9​	text XBEA​
10​	XXXX​
11​	text XBEA text​
12​	blu XXXX bla​
13​	bhyr text​
14​	XYZW​
15​	text text​
16​	XYZW Text​
17​	blah​
18​	blah bla​

<tbody>
</tbody>

Try this code

Sub aTest()
    
    With ActiveSheet
        'Create the criteria range in range C1:C2 using a formula
        .Range("C1").Value = "Formula"
        .Range("C2").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList,A2)),0))"
        
        'Apply advanced filter in data range
        .Range("$A$1:$A$" & .Cells(.Rows.Count, "A").End(xlUp).Row). _
            AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("C1:C2"), _
            Unique:=False
    End With
End Sub

You get

	A​	B​	C​	D​	E​
1​	Code​		Formula​		MyList​
2​	text A717​		TRUE​		A717​
4​	A717​				XXXX​
6​	XBEA bla​
7​	A717 text​
9​	text XBEA​
10​	XXXX​
11​	text XBEA text​
12​	blu XXXX bla​
14​	XYZW​
16​	XYZW Text​

<tbody>
</tbody>

Hope this helps

M.

 

[Jvalades]

This worked wonderfully. Thanks for the help everyone! Many more questions to come!

 

[Marcelo Branco]

This worked wonderfully. Thanks for the help everyone! Many more questions to come!

You are welcome. Glad to help.

M.

 

[Jvalades]

You are welcome. Glad to help.

M.

M,

Best day to you,

I have another question with this code.

So this is what I have right now for one filter

Sheets(2).Select    Range("AA3").Select
    ActiveCell.FormulaR1C1 = "XBEA"
    Range("AA4").Select
    ActiveCell.FormulaR1C1 = "A7171"
    Range("AA3:AA4").Select
    ActiveWorkbook.Names.Add Name:="MyList", RefersToR1C1:= _
        "='L and B'!R3C27:R4C27"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=MyList"
    Range("AA3").Select
    
     With ActiveSheet
        'Create the criteria range in range Y2:Y3 using a formula
        .Range("Y2").Value = "Formula"
        .Range("Y3").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList,D1)),0))"
        
        'Apply advanced filter in data range
        .Range("$D$1:$D$" & .Cells(.Rows.Count, "D").End(xlUp).Row). _
            AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Y2:Y3"), _
            Unique:=False
    End With

I'm sure I can simplify this but with my skill level this is as good as it gets haha.

So the filter works perfectly except it somehow pulls this Part number "C39151A2900000".

Back information: there are in this example three other parts with that same number and somehow it filters them out but leaves that one.

Thanks for your continuous help.

 

[Marcelo Branco]

Sorry, i'm confused...
Your code uses different worksheets
Sheets(2) to create the list
'L and B'!R3C27:R4C27" to create the named range
Activesheet...

Could you please clarify:
In what Sheet are the data and the criteria_range?
What is the active sheet when you run the code?

M.

 

[Marcelo Branco]

Another question
Is there a header in D1?

There must be a header in D1 and the data must be in D2 and following rows.
Thus the formula (criteria) should be adjusted to
.Range("Y3").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList,D2)),0))"

M.