Excel VBA Autofilter using array - multiple values in same column

Question

I feel like the answer is out there, but after lots of searching and experimenting, I am still coming up short.

So can see in the first image that column O had a comma separated list of values. I would like my routine to filter the data on column A using the entire list when the user double click on the cell containing the list.

My code reads:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("O:O")) Is Nothing Then
    Sheet1.Cells.AutoFilter 'clear existing filters
    Dim idArray() As String
    idArray = Split(Target.Value, ",") 'store cell contents in array
    Dim newIDArray(0 To 100) As String
    Dim i As Long
    For i = 0 To UBound(idArray)
     newIDArray(i) = """" & CStr(idArray(i)) & """"   'wrap elements with quotes ... not sure if needed
    Next
    Sheet1.Range("$A$8").AutoFilter Field:=1, Criteria1:=newIDArray
End If

Cancel = False
End Sub

However the result is the following image. I looks like it is taking the filter in column A, deselecting All and showing results .... it is not using the values from the comma delimited list at all.

Any thoughts on what is happening? Thanks for reading.

I would debug this by checking the values of your final array before filtering. There is likely something wrong with the way you have loaded it — urdearboy
– urdearboy, Commented Aug 21, 2019 at 22:10

urdearboy · Accepted Answer · 2019-08-21 21:59:37Z

2

On my setup:

The comma separated values correspond to Range("J1:J3")
The range to filter correspond to Range("A1:A18")

Please see correct way to appropriately size (ReDim) an array and how to add values to it below

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Arr          'Array to SPLIT string
Dim i As Long    'Index to loop through Arr
Dim Filt         'Array to filter range

If Not Intersect(Target, Range("J1:J3")) Is Nothing Then
    Arr = Split(Target, ",")
    ReDim Filt(LBound(Arr) To UBound(Arr))
        For i = LBound(Arr) To UBound(Arr)
            Filt(i) = CStr(Arr(i))
        Next i
    Range("A1:A18").AutoFilter 1, Filt, xlFilterValues
End If

End Sub

answered Aug 21, 2019 at 21:59

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

urdearboy Over a year ago

I removed the filter clear at beginning just for solution.

Rafał B. Over a year ago

@Saladsamurai you may also add one condition e.g. "And Len(Target.Value) > 0" after "If Not Intersect..." to prevent error if someone will click empty cell, or add more advanced check for proper Target values

urdearboy Over a year ago

True @RafałB - if OP limits the intersect to a range rather an entire column, the check may not be necessary but it def doesn’t hurt to have it!

Collectives™ on Stack Overflow

Excel VBA Autofilter using array - multiple values in same column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related