Filter a column using VBA with multiple conditions

[sobeitjedi]

Hi.

I have a line within a macro which isn't working:

Selection.AutoFilter field:=3, Operator:=xlFilterValues, Criteria1:=Array("*gala*", "*onep*", "*ipho*")

I'm trying to filter a column to show all results where onep, ipho or gala is in the cell in the column

How do I correct?

 

[sobeitjedi]

Anyone?

 

[DanteAmor]

Hi.

I have a line within a macro which isn't working:

Selection.AutoFilter field:=3, Operator:=xlFilterValues, Criteria1:=Array("*gala*", "*onep*", "*ipho*")

I'm trying to filter a column to show all results where onep, ipho or gala is in the cell in the column

How do I correct?

Try this

Sub Filter_with_multiple_conditions()
    Dim r As Range, f As Range, cell As String, n As Long, i As Long
    Dim crit() As Variant, arr() As Variant
    
    crit = Array("*gala*", "onep", "ipho") 'Enter criteria with or without "*"
    Set r = ActiveSheet.UsedRange
    r.AutoFilter
    n = 0
    For i = 0 To UBound(crit)
        Set f = r.Find(crit(i), LookIn:=xlValues, lookat:=xlPart)
        If Not f Is Nothing Then
            cell = f.Address
            Do
                ReDim Preserve arr(n)
[COLOR=#0000ff]                arr(n) = f.Value[/COLOR]
                n = n + 1
                Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
        End If
    Next
[COLOR=#0000ff]    r.AutoFilter Field:=3, Criteria1:=arr, Operator:=xlFilterValues[/COLOR]
End Sub