Autofilter using Array & loop

Hi,

can anyone help me so that it works.

Dim rng As Range
Dim i, lng As Long
Dim sCriteria As Variant

            i = Range("G" & Rows.Count).End(xlUp).Row
      Set rng = Range("A12:TB" & i)
    sCriteria = Array("BC, HEA*", "Deputy Hea*", "Head, Bi*")
    
    
    For lng = LBound(sCriteria) To UBound(sCriteria)
       With rng
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:="Business Banking"
            .AutoFilter Field:=6, Criteria1:=sCriteria(lng)
        End With
    Next lng

try this....

Dim rng As Range
Dim i, lng As Long
Dim sCriteria As Variant

            i = Range("G" & Rows.Count).End(xlUp).Row
      Set rng = Range("A12:TB" & i)
    sCriteria = Array("BC, HEA*", "Deputy, Hea*", "Head, Bi*")
    
    
    For lng = LBound(sCriteria) To UBound(sCriteria)
       With rng
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:="Business Banking"
            .AutoFilter Field:=6, Criteria1:=sCriteria(lng)
        End With
    Next lng

Don't forget to click *

hi vikas,

There is no difference with my original post. at which point you make correction?,

I want the filter to display all sCriteria.

Hi GIS2013,

how to copy the result of each loop?

Hi Faridwahidi,
maybe something like this

Sub ertert()
Dim r As Range, adr As String, sCriteria As Variant, v, f$
sCriteria = Array("BC, HEA", "Deputy Hea", "Head, Bi"): f = "~"
With Range("A12:TB" & Cells(Rows.Count, "G").End(xlUp).Row)
    With .Columns(6)
        For Each v In sCriteria
            Set r = .Find(What:=v)
            If Not r Is Nothing Then
                adr = r.Address
                Do
                    If InStr(f, "~" & r.Value & "~") = 0 Then f = f & r.Value & "~"
                    Set r = .FindNext(r)
                Loop While r.Address <> adr
            End If
        Next v
    End With
        .AutoFilter
        .AutoFilter 2, "Business Banking"
        .AutoFilter 6, Split(Mid(f, 2), "~"), 7
End With
End Sub

Hi nilem,

Thanks a lot. Your code working fine. for me, it is quite tough to understand this part. if possible, can you explain to me?

For Each v In sCriteria
            Set r = .Find(What:=v)
            If Not r Is Nothing Then
                adr = r.Address
                Do
                    If InStr(f, "~" & r.Value & "~") = 0 Then f = f & r.Value & "~"
                    Set r = .FindNext(r)
                Loop While r.Address <> adr
            End If
        Next v

GIS2013 ,
Your code has error while running on .AutoFilterMode = False