Wrong filtering of rows on one sheet based on an array with values ​​from another sheet in VBA

Question

My intention was for the following code to compile the data from my "Low CPM 1" worksheet into an array, and then filter my active sheet based on this array. Although the macro seems to affect the filters, none of the values ​​are filtered out. Any help on this subject would be greatly appreciated.

  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

Answer 1

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

Alternative

• , . ,
• , , , .
• .

, , . 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