CODEWITHSUNDEEP
vbaexcel
Tom Ruiz
Tom Ruiz

Reputation: 307

Use a Range as Array for Criteria for AutoFilter Method

Good day,

I have the following code

Sub AutFilCrit()



With Worksheets("Email Campaign Stats")
                .AutoFilterMode = False
                .Range("A6:AP6").AutoFilter
                .Range("A6:AP6").AutoFilter Field:=5, Criteria1:=Array("OARGUELLO", "KARAUZ", "LBALLADARES"), Operator:=xlFilterValues


End With
End Sub

As you can see the criteria is an array of names written directly into the code. My question is: lets say I had a list of names in a range A1:A10 or A1:K1, how can I use that range as the Array for the Criteria

Upvotes: 1

Views: 1764

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Here is an example that builds an array from three cells in Sheet2 to filter a column in Sheet1:

Sub UsingARange()
    Dim arr(1 To 3) As String
    For i = 1 To 3
        arr(i) = Sheets("Sheet2").Cells(i, 1).Value
    Next i

    With Sheets("Sheet1").Range("A1:A9")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    End With
End Sub

If you want to avoid the loop:

Sub UsingARange2()
    Dim rng As Range

    Set rng = Sheets("Sheet2").Range("A1:A3")
    arr = Application.WorksheetFunction.Transpose(rng.Value)
    With Sheets("Sheet1").Range("A1:A9")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    End With
End Sub

Upvotes: 3

Related Questions