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

 

[Jvalades]

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.

D1 = Part number header with data in D2 and following rows.

Sheet two is named L & B, as you described, so it is only using 1 worksheet.

So for this example just say the sheet is Sheet(2) and data range is $D$1:$D$ (all of D).

The active sheet is sheet two.

 

[Jvalades]

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.

So I tested it by extending "MyList" to include an extra cell XBEA, A717 and A312

And what it is doing is filtering by all of those but pulling the next row below the filtered search

ex.

W	<-
W	<-
W	<-
X	<-
X
X
Y	<-
Y	<-
Y	<-
Z	<-
Z
Z

<tbody>
</tbody>

If I am searching for W and Y. After it performs the filter it pulls the first X and the First Z.

The finished result is:

W	<-
W	<-
W	<-
X	<-
Y	<-
Y	<-
Y	<-
Z	<-

<tbody>
</tbody>

Hope this clarifies things.
Best,

 

[Marcelo Branco]

The code below worked perfectly for me.

Data in Sheet L and B

Before filter

	D​
1​	Code​
2​	text A7171​
3​	text A718​
4​	A7171​
5​	tyio​
6​	XBEA bla​
7​	A7171 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 A312​
18​	blah bla​
19​	A312​

<tbody>
</tbody>

Run this code

Sub aTest()
    With Sheets("L and B")
        'Insert values of interest in cells AA3:AA5
        .Range("AA3").Value = "XBEA"
        .Range("AA4").Value = "A7171"
        .Range("AA5").Value = "A312"
        
        'Create named range MyList
        .Names.Add Name:="MyList", RefersTo:="='L and B'!$AA$3:$AA$5"
        
        'Put a header in AA2
        .Range("AA2").Value = "MyList"
     
        'Create the criteria range in range Y2:Y3 using a formula
        .Range("Y2").Value = "Formula"
        .Range("Y3").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList,D2)),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
End Sub

After Filter

	D​
1​	Code​
2​	text A7171​
4​	A7171​
6​	XBEA bla​
7​	A7171 text​
9​	text XBEA​
11​	text XBEA text​
17​	blah A312​
19​	A312​

<tbody>
</tbody>

Hope this helps

M.

 

[Marcelo Branco]

oops... is missing a dot
.Range("Y2:Y3")
in this code line

'Apply advanced filter in data range
.Range("$D$1:$D$" & .Cells(.Rows.Count, "D").End(xlUp).Row). _
      AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=[SIZE=1][B][COLOR=#ff0000].[/COLOR][/B][/SIZE]Range("Y2:Y3"), _
      Unique:=False

M

 

[Jvalades]

Your code is so fluent!!

I will test this and get back to you.

Thanks for the continued support.

 

[Jvalades]

This worked.

You are the best.

Many more questions to come!

 

[Marcelo Branco]

Glad to help

M.

 

[Jvalades]

M.

So the purpose of this macro was to filter for "MyList". Can you somehow reverse this macro to filter OUT "MyList"?

Best,
JV

 

[Marcelo Branco]

M.

So the purpose of this macro was to filter for "MyList". Can you somehow reverse this macro to filter OUT "MyList"?

Best,
JV

Change this code line

.Range("Y3").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList,D2)),0))"

to

.Range("Y3").Formula = "=[COLOR=#0000ff][B]NOT([/B][/COLOR]OR(INDEX(ISNUMBER(SEARCH(MyList,D2)),0))[COLOR=#0000ff][B])[/B][/COLOR]"

M.

 

[Jvalades]

Change this code line

.Range("Y3").Formula = "=OR(INDEX(ISNUMBER(SEARCH(MyList,D2)),0))"

to

.Range("Y3").Formula = "=[COLOR=#0000ff][B]NOT([/B][/COLOR]OR(INDEX(ISNUMBER(SEARCH(MyList,D2)),0))[COLOR=#0000ff][B])[/B][/COLOR]"

M.

Ahh Great!