Excel VBA : how to filter out an array

Question

如何通过 VBA 过滤出数组。我的意思是，我想排除 arr来自归档 29 的数组。arr是一个很长的列表。以下片段 不 工作。

arr = Array("<>0735", "<>801124", "<>0613","<>0921", "<>1086", "<>0949", "<>0494", "<>0767", "<>0739")
MySheet.Range("AB1").AutoFilter _
    Field:=29, _
    Criteria1:=arr, _
    Operator:=xlFilterValues

Answer 1

如果要使用 AutoFilter 并排除多个值，请创建一个仅包含“好”值的数组。假设我们从:

我们想排除 Stooges。我们需要创建一个“Stooge-free”数组”:

Sub NoStooges()
    Dim rng As Range, c As Collection
    Dim r As Range, v As String, n As Long
    Dim i As Long, arr
    
    Set rng = Range("A2:A20")
    Set c = New Collection
    
    For Each r In rng
        v = r.Value
        If v <> "Larry" And v <> "Moe" And v <> "Curley" Then
            On Error Resume Next
                c.Add v, CStr(v)
            On Error GoTo 0
        End If
    Next r
    
    n = c.Count
    
    ReDim arr(1 To n)
    For i = 1 To n
        arr(i) = c.Item(i)
    Next i
    
    With ActiveSheet
        If .FilterMode Then Cells.AutoFilter
        .Range("$A$1:$A$20").AutoFilter
        .Range("$A$1:$A$20").AutoFilter Field:=1, Criteria1:=(arr), Operator:=xlFilterValues
    End With
        
    
End Sub

数组 arr及其关联的 Collection 只有三个元素 {a, b, c} 。代码产生: