Filter using array criteria

[Bill_Kro]

I want to filter a table using a range that has the values to filter on.

Range: (in cells A5:A7)
Apple
Pear
Cherry

Table: (Table1)
Apple
Pear
Banana
Strawberry

I am trying to pass the values of the range to an array and then filter the table using the array values as the criteria.
The problem is the the table is only filtering on the first value (apple)

Below is the code I'm using:

Sub Filter_Use_Array_Test2()

Dim Selctd_Cells As Range
Dim Fltr_Criteria() As Variant

Set Selctd_Cells = Range("A5:A7")
Fltr_Criteria = Selctd_Cells.Value

With ActiveSheet.ListObjects("Table1").Range
.AutoFilter Field:=1, _
Criteria1:=Fltr_Criteria, _
Operator:=xlFilterValues
End With

End Sub

As a related but separate question, I am new to using arrays. How do I check to make sure the array has the correct values? How do I paste the array?

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

 

[igold]

How about something like this...

Sub Filter_Use_Array_Test2()
    
    Dim Selctd_Cells


    Selctd_Cells = Range("A5:A7")
    Selctd_Cells = Application.Transpose(Selctd_Cells)
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        Array(Selctd_Cells), Operator:=xlFilterValues
    
End Sub

 

[Bill_Kro]

This code does work, but is there a way to make if more generalized?
If the range was horizontal and not vertical then I don't think it would work.

Ultimately, I want to ask the user to select the cells that would be used as the filter criteria.
I was then hoping that I could pass the values of the selected range into the array.
Then I would use the array as the filter criteria in the table.

 

[igold]

How about this...

Sub Filter_Use_Array_Test2()
    
    Dim Selctd_Cells()


    Selctd_Cells = Application.InputBox(prompt:="Please select the criteria range", Type:=64)
    On Error GoTo Horz
    If UBound(Selctd_Cells, 2) > 0 Then
        Selctd_Cells = Application.Transpose(Selctd_Cells)
    End If
Horz:
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        Array(Selctd_Cells), Operator:=xlFilterValues
        
End Sub