VBA Excel: auto filter based on a range

Hello experts,

I have the following code that allows me to filter a column based on a selected range:

I would like to add the following:

Based on the activeCell value I would like to update the first value of the arr range however I don't know how to identify the first Range of a selected range through an inputbox in that case arr range.

I tried arr(1).adress.value = av but it doesnt work.

Thank you very much for your help.

 
 Sub auto_filter_based_on_range()


Dim Arr As Variant
Dim cn As Integer
Dim av As string
Dim i As Integer
On Error GoTo exit_sub
    av=ActiveCell.value
    cn = ActiveCell.Column
    
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
 
 MsgBox (ActiveCell.Value)
 
 Arr = Application.InputBox(prompt:="Enter the range to filter", Type:=8)
 
 
 Arr2 = Application.Transpose(Arr)
 
 For i = LBound(Arr2) To UBound(Arr2)
        Arr2(i) = CStr(Arr2(i))
Next i
 
ActiveSheet.UsedRange.AutoFilter Field:=cn, Criteria1:=Arr2, Operator:=xlFilterValues
exit_sub:
End Sub

Open in new window

LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff DarlingDeveloper AnalystCommented:
I think adding a default to the inputbox for the range may help.  attached is a sample.
ee_2015092901.xlsm
LD16Author Commented:
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

Open 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.
Jeff DarlingDeveloper AnalystCommented:
how about this?

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

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

byundtMechanical EngineerCommented:
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

Open 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
LD16Author Commented:
@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:
2015-09-29-20_49_25-Microsoft-Excel-.png
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.
byundtMechanical EngineerCommented:
I'm going to assume that your additional requests are equivalent to:
1. After filtering the range, highlight the visible rows in yellow
2. If no rows were hidden by the filter, then display a warning message
3. If the data didn't contain any of the values in the filter, then display a warning message
Sub auto_filter_based_on_range()

Dim Arr As Variant, Arr2 As Variant
Dim ar As Range, rg As Range, rgFiltered As Range, rgg As Range
Dim cn As Integer
Dim av As String
Dim i As Integer
Dim nVisibleRowsInitial As Long, nVisibleRowsFinal As Long
    
With ActiveCell
    av = .Value
    cn = .Column
    Set rgFiltered = .CurrentRegion
    If rgFiltered.Rows.Count < 2 Then
        MsgBox "You must select a cell within the range to be filtered before running the macro."
        Exit Sub
    End If
    Set rgg = rgFiltered.Cells(2, 1).Resize(rgFiltered.Rows.Count - 1, rgFiltered.Columns.Count)
End With
 
 MsgBox (ActiveCell.Value)
 
 If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
 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
 
 rgg.Cells.Interior.ColorIndex = xlNone
 For Each ar In rgFiltered.Areas
    nVisibleRowsInitial = nVisibleRowsInitial + ar.Rows.Count
 Next
 
 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
 
rgFiltered.AutoFilter Field:=cn, Criteria1:=Arr, Operator:=xlFilterValues
Set rgFiltered = rgFiltered.SpecialCells(xlCellTypeVisible)
For Each ar In rgFiltered.Areas
    nVisibleRowsFinal = nVisibleRowsFinal + ar.Rows.Count
Next

Select Case nVisibleRowsFinal
Case 1
    MsgBox "The data didn't contain any of the filter values"
Case nVisibleRowsInitial
    MsgBox "No rows were hidden by filtering"
Case Else
    rgg.SpecialCells(xlCellTypeVisible).Cells.Interior.ColorIndex = 6
End Select

End Sub

Open in new window

AutoFilterUsingVBAarrayQ28733702.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Test it and it works perfectly. Thank you again for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.