Auto Filter text that contains...

[tennis07]

Hi Guys,
How can I create an autofilter in many columns that contains an specific text.
If I do something like that (below), the return will be empty because it works like an AND operator and I want to work with OR.
Column B = Red OR Column C = Red

ActiveSheet.Range("$A$1:$E$1000").AutoFilter Field:=1, Criteria1:= _
"Red", Operator:=xlOr, Field:=2, Criteria2:="Red"

How can I get a result like the image attached?

Any help?

Thanks in advance

 

[Marc L]

Hi,​

​

for an 'OR' operator between columns you have two choices :​

1. a helper column with a formula
   
   
2. an advanced filter …

 

[Marcelo Branco]

Try Advanced Filter with a formula as criterion

Something like this

Sub AdvFilter()
    Range("G1").Value = "Formula"
    Range("G2").Formula = "=MATCH(""Red"",B2:C2,0)"
    Range("A1:E21").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:G2"), CopyToRange:=Range("K1:O1"), Unique:=False
End Sub

Hope this helps

M.

 

[tennis07]

Try Advanced Filter with a formula as criterion

Something like this

Sub AdvFilter()
    Range("G1").Value = "Formula"
    Range("G2").Formula = "=MATCH(""Red"",B2:C2,0)"
    Range("A1:E21").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:G2"), CopyToRange:=Range("K1:O1"), Unique:=False
End Sub

Hope this helps

M.

 

[tennis07]

Marcelo,

thanks a lot.

Another question...How can I replace the "red" text for a variable instead?
=MATCH(variable, range,0)

 

[Marcelo Branco]

Another question...How can I replace the "red" text for a variable instead?
=MATCH(variable, range,0)

Try

Sub AdvFilter()
    Dim myVal As String
    
    myVal = "Red"
    Range("G1").Value = "Formula"
    Range("G2").Formula = "=MATCH(""" & myVal & """,B2:C2,0)"
    Range("A1:E21").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:G2"), CopyToRange:=Range("K1:O1"), Unique:=False
End Sub

M.