excel - Excel VBA：如何过滤出数组

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

标签： excelvbafilter

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} 。代码产生：