使用数组的 VBA 自动过滤器 - 如果它不在过滤列表中，则忽略条件

VBA autofilter using an array - ignore criteria if it is not in the filtered list

提问by Mr_Oppenheimer

I have been looking all over for a solution to this VBA autofiltering issue, any ideas are appreciated:

我一直在寻找解决这个 VBA 自动过滤问题的方法，任何想法都值得赞赏：

I have a static list of autofilter criteria in a named range "FslList" - which i have converted into one dimention array for autofiltering column 14 in a data worksheet:

我在命名范围“FslList”中有一个自动过滤条件的静态列表 - 我已将其转换为一维数组，用于自动过滤数据工作表中的第 14 列：

   Dim FSLArray As Variant
        Dim rngFSL As Range
        Set rngFSL = RawData.Worksheets(1).Range("FslList")
        FSLArray = rngFSL.Value

        With NewBook.Worksheets(1)
          .Cells.AutoFilter Field:=14, Criteria1:=Application.Transpose(FSLArray), Operator:=xlFilterValues

Once i filter out the values from the array - i need to delete them

一旦我从数组中过滤掉值 - 我需要删除它们

          With .AutoFilter.Range
            Set DeleteRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
          End With

          DeleteRange.EntireRow.Delete
          NewBook.Worksheets(1).AutoFilterMode = False
        End With

My issue is that my list of data is allways changing, and not all the values from FSLArray are in the column to be filtered. Thus the autofilter stops, once it encounters a criteria that is not on the list - and does not include any following criteria when filtering.

我的问题是我的数据列表总是在变化，并不是 FSLArray 中的所有值都在要过滤的列中。因此，一旦遇到不在列表中的标准，自动过滤器就会停止 - 并且在过滤时不包括任何以下标准。

What i would like to do is for the autofilter to continue filtering using other array criteria, if one or more of the elements in the array, is not found amongst the data to be filtered.

如果在要过滤的数据中找不到数组中的一个或多个元素，我想要做的是让自动过滤器继续使用其他数组标准进行过滤。

EDIT: i have changed the data in my array from numbers (which it is) to letters - it works fine with letters now.

编辑：我已经将数组中的数据从数字（它是）更改为字母 - 现在它可以很好地处理字母。

I have tried re-writing the code and define a named range as suggested:

我尝试重新编写代码并按照建议定义一个命名范围：

Elements i have in the array (range C11:C14) are:

我在数组中的元素（范围 C11:C14）是：

Acc
9158
11958 (this one is not present in the list of data)
15938
15940

The named range "PODCustList" is defined as follows:

命名范围“PODCustList”定义如下：

=OFFSET(Acc,1,0,COUNTA(Settings!$C:$C)-1,1)

The code is the same:

代码是一样的：

Dim PODCustArray As Variant
Dim rngPODCust As Range
Set rngPODCust = RawData.Worksheets(1).Range("PODCustList")
PODCustArray = rngPODCust.Value

With Worksheets(1)
  .Cells.AutoFilter Field:=7, Criteria1:=Application.Transpose(PODCustArray), Operator:=xlFilterValues

What i get in return after filtering is only rows with "9158" element in them filtered.

过滤后我得到的回报只是其中过滤了“9158”元素的行。

SOLVED: I needed to run my array through this - Criteria1:=Split(Join(Application.Transpose(PODCustArray)))for autofilter to correctly interpret the data within as a string array.

已解决：我需要通过此运行我的数组 -Criteria1:=Split(Join(Application.Transpose(PODCustArray)))以便自动过滤器将其中的数据正确解释为字符串数组。

Can i adapt my code, or do i need to use a different approach?

我可以调整我的代码，还是需要使用不同的方法？

Thank you in advance,

先感谢您，

回答by Siddharth Rout

My issue is that my list of data is allways changing, and not all the values from FSLArray are in the column to be filtered. Thus the autofilter stops, once it encounters a criteria that is not on the list - and does not include any following criteria when filtering.

我的问题是我的数据列表总是在变化，并不是 FSLArray 中的所有值都在要过滤的列中。因此，一旦遇到不在列表中的标准，自动过滤器就会停止 - 并且在过滤时不包括任何以下标准。

It depends on how have you defined your Range("FslList")

这取决于你如何定义你的 Range("FslList")

See this example

看这个例子

I have a workbook which has Sheet1and Sheet5. Sheet1has the list and Sheet5has the data which needs to be filtered. The workbook can be downloaded from HERE

我有一本工作簿，其中有Sheet1和Sheet5。Sheet1有列表并Sheet5有需要过滤的数据。工作簿可以从这里下载

Now select A1in Sheet1and give it a name, say, Criterias. Next create a name called FslListin the Name Manager and set the formula as =OFFSET(Criterias,1,0,COUNTA(Sheet1!$A:$A)-1,1)

现在选择A1中Sheet1，并给它一个名字，说Criterias。接下来FslList在名称管理器中创建一个名称并将公式设置为=OFFSET(Criterias,1,0,COUNTA(Sheet1!$A:$A)-1,1)

Now run this code

现在运行这段代码

Option Explicit

Sub Sample()
    Dim FslList As Variant
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngCritList As Range, rngSh5 As Range

    Set ws1 = Worksheets("Sheet5")
    Set ws2 = Worksheets("Sheet1")

    Set rngSh5 = ws1.Range("$A").CurrentRegion
    Set rngCritList = ws2.Range("FslList")

    FslList = rngCritList.Value

    rngSh5.AutoFilter Field:=1, _
                      Criteria1:=Application.Transpose(FslList), _
                      Operator:=xlFilterValues
End Sub

You will see that the list gets filtered even when eeeis there in the criteria list but not in the list that needs to be filtered.

即使eee在条件列表中但不在需要过滤的列表中，您也会看到该列表被过滤。

This is how the Sheet5gets filtered after you run the macro

这是Sheet5运行宏后过滤的方式

回答by Mr_Oppenheimer

Issue solved at last. The code was fine as it was when using with strings containing letters. However I needed to run my array through this - Criteria1:=Split(Join(Application.Transpose(PODCustArray)))for autofilter to correctly interpret the data within as a string array.

问题终于解决了。代码很好，就像使用包含字母的字符串一样。但是，我需要通过此运行我的数组 -Criteria1:=Split(Join(Application.Transpose(PODCustArray)))以便自动过滤器将其中的数据正确解释为字符串数组。