根据具有VBA中另一个工作表的值的数组，在一个工作表上过滤掉一些行 [英] Trouble filtering out rows on one worksheet based on an array with values from another worksheet in VBA

问题描述

我的目的是让下面的代码将数据从低CPM 1工作表中编译成一个数组，然后根据这个数组过滤我的活动工作表。虽然宏似乎影响过滤器，但是没有一个值被过滤掉。任何关于此事的帮助将不胜感激。

  Sub Macro1（）
 
 Dim CPM1Array（0 To 300）As Variant 
 
对于i = 2 To UBound（CPM1Array）
 CPM1Array（i）=表格（低CPM 1）。单元格（i，2）.Value 
 Next Next 
 
 ActiveSheet.Range（$ A $ 1：$ H $ 251）。AutoFilter Field：= 3，Criteria1：=（<> 1 to Ubound（CPM1Array）运算符：= xlFilterValues 
 
 End Sub 
  

解决方案

自动过滤器没有简单的方法来实现你想要的。您不能使用 Criteria1：=<> MyArray

替代 / p>

1. 我们知道我们不想要哪些值。我们可以找到相关列的值。


2. 只需将相关列的值存储在数组中，然后通过将其与具有


3. 从数组中删除空白单元格


4. 将最终的数组传递给自动过滤器。

动作

我们的工作表如下图所示图片。我只举一个例子，只有15行。

代码

  Sub Sample（）
 Dim ws As Worksheet 
 Dim MyAr（1到5）As String 
 Dim tmpAr As Variant，ArFinal（）As String 
 Dim LRow As Long 
 
 ReDim ArFinal（0 To 0）
 
设置ws = ActiveSheet 
 
'~~>创建一个我们不想要的值的数组
对于i = 1到5 
 MyAr（i）= i 
下一个i 
 
与ws 
 ~~>最后一排Col C sice你将过滤第三列
 LRow = .Range（C& .Rows.Count）.End（xlUp）.Row 
 
'~~> ;将值C形式存储在数组
 tmpAr = .Range（C2：C& LRow）.Value 
 
'~~>比较和删除我们不想要的值
对于i = 1 To LRow  -  1 
对于j = 1对于UBound（MyAr）
如果tmpAr（i，1）= MyAr（j）然后tmpAr（i，1）=
下一个j 
下一个i 
 
'~~>通过将数组复制到新数组中，从空格中删除空白元素
 For i = LBound（tmpAr）To UBound（tmpAr）
如果tmpAr（i，1） 然后
 ArFinal（UBound（ArFinal））= tmpAr（i，1）
 ReDim保留ArFinal（0到UBound（ArFinal）+ 1）
结束If 
下一个
 
'~~>过滤您想要的值。更改范围适用
 .Range（$ A $ 1：$ H $ 15）。AutoFilter Field：= 3，Criteria1：= ArFinal，Operator：= xlFilterValues 
 End with 
 End Sub 
  

输出

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

  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

解决方案

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

Alternative

1. We know which values we do not want. We can find out what are the values of the relevant column
2. 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.
3. Remove blank cells from the array
4. Pass the final array to the 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.

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

这篇关于根据具有VBA中另一个工作表的值的数组，在一个工作表上过滤掉一些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！