CODEWITHSUNDEEP
vbaexcel
Abhii
Abhii

Reputation: 295

VBA Autofilter (Text Filters) With Multiple Criteria of "begin with"

ActiveSheet.Range("F_Item").AutoFilter Field:=1, Criteria1:=Array("ca*", "inc*", "ps*"), Operator:=xlFilterValues

I am trying to filter column with multiple criteria using array, the condition is to filter the values which starts with ca, or starts with inc, or start with ps.

ActiveSheet.Range("F_Item").AutoFilter Field:=1, Criteria1:="=ca*", Operator:=xlOr, Criteria2:="=inc*"

It's working this way, but its limited to search for two conditions only.

Upvotes: 1

Views: 18713

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

With data like:

enter image description here

Running this macro:

Sub WildAutofilter()
    Dim data As Range, c As Collection
    Dim v As String, i As Long, ary
    Set data = Range("A1:A23")
    Set c = New Collection

    On Error Resume Next
        For i = 2 To 23
            v = Cells(i, 1).Value
            If Left(v, 2) = "ps" Or Left(v, 2) = "ca" Or Left(v, 3) = "inc" Then
                c.Add v, CStr(v)
            End If
        Next i
    On Error GoTo 0

    ReDim ary(0 To c.Count - 1)
    For i = 1 To c.Count
        ary(i - 1) = c.Item(i)
    Next i

    With ActiveSheet.Range("$A$1:$A$23")
        .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
    End With
End Sub

Will produce:

enter image description here

Upvotes: 1

amg
amg

Reputation: 141

Try to use Advance filter 

Range("C3:C20").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("D3:D6"), Unique:=False

Upvotes: 1

Related Questions