VBA Excel: auto filter based on a range

I think adding a default to the inputbox for the range may help.  attached is a sample.
ee_2015092901.xlsm

Thank you for this proposal.

The problem here is that I want to identify the first range of selected range:

Here is what I am able to identify with:

Sub idenfity_first_range()


Dim Arr As Range
Dim Arrbis As Range

   
 
    
 'Arr = Application.InputBox(prompt:="Enter the range to filter", Type:=8, Default:=ActiveSheet.UsedRange.Adress)
Set Arrbis = ActiveSheet.UsedRange.Cells.SpecialCells(xlConstants)(1)
'Arrbis = Arr.Cells.SpecialCells(xlConstants)(1)
MsgBox Arrbis.Column
MsgBox Arrbis.Row

 
 
End Sub

Select allOpen in new window

I don't know why I am not able to call Arr within Arrbis and when I define Arrbis with ActiveSheet.UsedRange I am able to get the column and row of the first range.

Probably it should be that when you make a select through an inputbox the range is taken into account through the address function. I don't know how can I manage this.

What it works:

Set Arrbis = ActiveSheet.UsedRange.Cells.SpecialCells(xlConstants)(1)


What it doesn't work:

Set Arrbis = Arr.Cells.SpecialCells(xlConstants)(1)

Thank you again for your help.

how about this?

Dim rngTest1
    
Set rngTest1 = Selection
MsgBox rngTest1.Column
MsgBox rngTest1.Row

Select allOpen in new window

In your original code, Application.InputBox(Type:=8) will return a range object. When you set the Variant arr equal to that range object, you get a two dimensional array in that Variant. I'd expect an error when you transpose that array and assign it to arr2--but only refer to the first dimension of Arr2 in statement 22. And because your error handling transferred control to exit_sub, nothing happened.

I rewrote your original code to return the range from Application.InputBox to a range variable. I then picked the values in the first column of that range and assigned them to a one-dimensional array. This latter array could be used in the AutoFilter.

Sub auto_filter_based_on_range()

Dim Arr As Variant, Arr2 As Variant
Dim rg As Range
Dim cn As Integer
Dim av As String
Dim i As Integer
    av = ActiveCell.Value
    cn = ActiveCell.Column
    
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
 
 MsgBox (ActiveCell.Value)
 
 On Error Resume Next
 Set rg = Application.InputBox(prompt:="Enter the range to filter", Type:=8)
 On Error GoTo 0
 If rg Is Nothing Then Exit Sub
 
 Arr2 = rg.Columns(1).Value
 ReDim Arr(1 To UBound(Arr2))
 
 For i = 1 To UBound(Arr2)
        Arr(i) = CStr(Arr2(i, 1))
Next i
 
ActiveSheet.UsedRange.AutoFilter Field:=cn, Criteria1:=Arr, Operator:=xlFilterValues
End Sub

Select allOpen in new window

If my code is not doing what you want, could you please describe step by step how you want things to work? For example:
1. Select cell C3
2. Run the macro
3. In response to Application.InputBox, select cells A2:A8
4. Column C should now be filtered for the values in A2:A8
AutoFilterUsingVBAarrayQ28733702.xlsm

@byundt: I tested your code and that is exactly what I want, to filter based on a range and to be able to keep the header and to check which are the values filtered.

I have just two little remarks:

1-Is it possible to highlight the the entire used row filtered in yellow? like this:

2-If I select a range which doesn't exist in the column filtered is there a way to exit the sub and msgbox("Any value in your range to filter exists in the range filtered")


Thank you again for this solution and for the very useful explanation concerning the variant // range object.

Test it and it works perfectly. Thank you again for your help!