Excel VBA自动筛选器使用数组-同一列中有多个值 [英] Excel VBA Autofilter using array - multiple values in same column

问题描述

我觉得答案就在那里，但是经过大量的搜索和试验，我仍然很短.

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

因此可以在第一张图片中看到O列具有逗号分隔的值列表.当用户双击包含该列表的单元格时，我希望我的例程使用整个列表来过滤A列上的数据.

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.

我的代码显示为:

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

但是结果如下图所示.我似乎正在采用A列中的过滤器，取消选择全部并显示结果....它根本没有使用逗号分隔列表中的值.

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.

推荐答案

在我的设置中:

• 逗号分隔的值对应于 Range("J1:J3")
• 要过滤的范围对应于 Range("A1:A18")

请在下面查看正确调整数组大小( ReDim )的正确方法，以及如何在其中添加值

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

这篇关于Excel VBA自动筛选器使用数组-同一列中有多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！