在VBA中，基于带有来自另一个工作表的值的数组过滤一个工作表上的行是有问题的

My intention was to have the following code compile data from my "Low CPM 1" worksheet into an array and then filter my active worksheet based on this array. While the macro does seem to affect the filters, none of the values get filtered out. Any help on this matter would be greatly appreciated

我的目的是让下面的代码从我的“低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

1 个解决方案

#1

There is no simple way with autofilter to achieve what you want. You cannot use Criteria1:="<>MyArray"

使用autofilter没有简单的方法来实现您想要的。你不能使用Criteria1:= " < > MyArray”

Alternative

替代

We know which values we do not want. We can find out what are the values of the relevant column
我们知道哪些值是我们不想要的。我们可以找出相关列的值
Simply store the values of the relevant column in an array and then remove the unnecessary values from it by comparing it with the array which has values we do not want.
简单地将相关列的值存储在一个数组中，然后将其与具有我们不想要的值的数组进行比较，从而删除它的不必要的值。
Remove blank cells from the array
从数组中删除空白单元格
Pass the final array to the autofilter.
将最后的数组传递给autofilter。

In Action

在行动

Let's say our worksheet looks like as shown in the below image. I am taking an example of only 15 rows.

假设我们的工作表如图所示。我举了一个只有15行的例子。

Code

代码

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

Output

输出

#1