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.


[TABLE="class: grid, width: 201"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]bbb[/TD]
[TD="align: center"]150[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]ccc[/TD]
[TD="align: center"]200[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]150[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]bbb[/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]vvv[/TD]
[TD="align: center"]200[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]150[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]ccc[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]ddd[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]sss[/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]bbb[/TD]
[TD="align: center"]100[/TD]
[/TR]
</tbody>[/TABLE]


Criteria range for the filter is:

[TABLE="class: grid, width: 201"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]45[/TD]
[TD="align: center"]aaa[/TD]
[/TR]
[TR]
[TD="align: center"]46[/TD]
[TD="align: center"]ccc[/TD]
[/TR]
[TR]
[TD="align: center"]47[/TD]
[TD="align: center"]bbb[/TD]
[/TR]
</tbody>[/TABLE]


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