在VBA中，如何基于另一个工作表中的值数组过滤出一个工作表上的行？

Question

我的本意是让以下代码将"Low CPM 1"工作表的数据编译进一个数组，然后根据该数组过滤我的活动工作表。虽然宏似乎确实影响了筛选器，但没有任何值被过滤掉。对此问题的任何帮助将不胜感激。

  Sub Macro1()

Dim CPM1Array(0 To 300) As Variant

For i = 2 To UBound(CPM1Array)
    CPM1Array(i) = Sheets("Low CPM 1").Cells(i, 2).Value
Next i

    ActiveSheet.Range("$A$1:$H$251").AutoFilter Field:=3, Criteria1:=("<>1 to Ubound(CPM1Array)"), Operator:=xlFilterValues

End Sub

- Andrew Pickett

---

尝试直接将条件设置到数组中，例如 ActiveSheet.Range("$A$1:$H$251").AutoFilter Field:=3, Criteria1:=CPM1Array, Operator:=xlFilterValues，它将根据数组中的值进行筛选。 - Mikegrann

Answer 1

使用自动筛选无法简单地实现你想要的功能。你不能使用Criteria1:="<>MyArray"

替代方案

1. 我们知道哪些值是不需要的。我们可以找出相关列的值。
2. 只需将相关列的值存储在数组中，然后通过与具有不需要的值的数组进行比较来从中删除不必要的值。
3. 从数组中删除空单元格
4. 将最终数组传递给自动筛选器。

操作演示

假设我们的工作表如下图所示。我仅以 15 行为例。

代码

Sub Sample()
    Dim ws As Worksheet
    Dim MyAr(1 To 5) As String
    Dim tmpAr As Variant, ArFinal() As String
    Dim LRow As Long

    ReDim ArFinal(0 To 0)

    Set ws = ActiveSheet

    '~~> Creating an array of values which we do not want
    For i = 1 To 5
        MyAr(i) = i
    Next i

    With ws
        '~~> Last Row of Col C sice you will filter on 3rd column
        LRow = .Range("C" & .Rows.Count).End(xlUp).Row

        '~~> Storing the values form C in the array
        tmpAr = .Range("C2:C" & LRow).Value

        '~~> Compare and remove values which we do not want
        For i = 1 To LRow - 1
            For j = 1 To UBound(MyAr)
                If tmpAr(i, 1) = MyAr(j) Then tmpAr(i, 1) = ""
            Next j
        Next i

        '~~> Remove blank cells from the array by copying them to a new array
        For i = LBound(tmpAr) To UBound(tmpAr)
            If tmpAr(i, 1) <> "" Then
                ArFinal(UBound(ArFinal)) = tmpAr(i, 1)
                ReDim Preserve ArFinal(0 To UBound(ArFinal) + 1)
            End If
        Next i

        '~~> Filter on values which you want. Change range as applicable
        .Range("$A$1:$H$15").AutoFilter Field:=3, Criteria1:=ArFinal, Operator:=xlFilterValues
    End With
End Sub

输出

- Siddharth Rout