Filter column based on another column

[m_sabeer]

Hi,
I wish to filter a column based on an input range in same (or another) column for eg.

	A	B
1	Account	Value
2	bbb	150
3	aaa	100
4	ccc	200
5	aaa	150
6	bbb	250
7	vvv	200
8	aaa	150
9	ccc	100
10	ddd	50
11	sss	250
12	bbb	100

<tbody>
</tbody>

Criteria range for the filter is:

	A
45	aaa
46	ccc
47	bbb

<tbody>
</tbody>

I am currently using a macro but that requires me to enter the range in the code:


Sub Test()
Dim Arr As Variant
Dim i As Integer
Arr = WorksheetFunction.Transpose(ActiveSheet.Range("A45:A47").Value)
For i = LBound(Arr) To UBound(Arr)
Arr(i) = CStr(Arr(i))
Next i
ActiveSheet.Range("$A$1:$A$12").AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues
End Sub


Can anyone help me create the macro where the 1st range is "selection" and criteria range is read from input box?

Thanks

 

[Fluff]

Not sure if I have understood you correctly, but try

Sub Test()
    Dim Arr As Variant
    Dim i As Integer
    Dim Rng As Range
    Dim Rws As Long
    
    Rws = InputBox("Please enter the number of criteria")
    Set Rng = Selection.Resize(Rws)
    Arr = WorksheetFunction.Transpose(Rng.Value)
    
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
    ActiveSheet.Range("$A$1:$A$12").AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues
End Sub

 

[m_sabeer]

Thanks fluff for the code. However, Rws = InputBox("Please enter the number of criteria") doesn't allow selection of range for the criteria range

Let me do a minor change in my requirement to make it simpler and understandable. I am looking for a code where the column to filter is column A (A:A) and criteria range is in some random place on the same sheet and can be selected and read by Input box.

Thanks

 

[Fluff]

If you select the first cell in the criteria range(A45, in your original post) & then enter the number of rows for the criteria in the input box (3 in you original post)then the code above should work.
Another option is you select the range where the criteria are (A45:A47, in your original post) & use this

Sub Test()
    Dim Arr As Variant
    Dim i As Integer
    
    Arr = WorksheetFunction.Transpose(Selection.Value)
    
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
    ActiveSheet.Range("$A$1:$A$12").AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues
End Sub

 

[m_sabeer]

worked like a charm Thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback