VBA自动过滤器使用数组 - 忽略条件，如果它不在过滤列表中 [英] VBA autofilter using an array - ignore criteria if it is not in the filtered list

问题描述

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

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

  Dim FSLArray As Variant 
 Dim rngFSL As Range 
设置rngFSL = RawData.Worksheets（1）.Range（FslList）
 FSLArray = rngFSL.Value 
 
使用NewBook.Worksheets（1 ）
 .Cells.AutoFilter字段：= 14，Criteria1：= Application.Transpose（FSLArray），运算符：= xlFilterValues 
  

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

 使用.AutoFilter。范围
设置DeleteRange = .Offset（1，0）.Resize（.Rows.Count  -  1）.SpecialCells（xlCellTypeVisible）
结束
 
 DeleteRange.EntireRow.Delete 
 N ewBook.Worksheets（1）.AutoFilterMode = False 
结束
  

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

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

编辑：我把数组中的数据从数字（它是）改为字母 - 现在可以使用字母。

编写代码并按照建议定义一个命名范围：

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

  Acc 
 9158 
 11958（这不存在于数据列表中）
 15938 
 15940 
  

命名范围PODCustList定义如下：

  = OFFSET（Acc，1,0，COUNTA（设置！$ C：$ C）-1,1）
  

代码是一样的：

  Dim PODCustArray As Variant 
 Dim rngPODCust As Range 
设置rngPODCust = RawData.Worksheets（1）.Range（PODCustList）
 PODCustArray = rngPODCust.Value 
 
带有工作表（1）
 .Cells.AutoFilter字段：= 7，Criteria1：= Application.Transpose（PODCustArray），运算符：= xlFilterValues 
  

过滤后我得到的返回值只有过滤掉了9158元素的行。

解决方案：我需要通过这个运行我的数组 - Criteria1：= Split（Join（Application.Transpose（PODCustArray））） for autofilter可以将数据正确解释为字符串数组。

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

提前谢谢，

解决方案

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

这取决于你如何定义你的 Range（FslList）

看到这个例子

我有一个工作簿有 Sheet1 和 Sheet5 。 Sheet1 具有列表， Sheet5 具有需要过滤的数据。该工作簿可从 HERE 下载

现在在 Sheet1 中选择 A1 ，并给它一个名称，例如指标分析。接下来在名称管理器中创建名为 FslList 的名称，并将公式设置为 = OFFSET（Criterias，1,0，COUNTA（Sheet1！$ A： $ A）-1,1）

现在运行此代码

  Option Explicit 
 
 Sub Sample（）
 Dim FslList As Variant 
 Dim ws1 As Worksheet，ws2 As Worksheet 
 Dim rngCritList As Range，rngSh5 As Range 
 
设置ws1 =工作表（Sheet5）
设置ws2 =工作表（Sheet1）
 
设置rngSh5 = ws1.Range（$ A $ 1） .CurrentRegion 
设置rngCritList = ws2.Range（FslList）
 
 FslList = rngCritList.Value 
 
 rngSh5.AutoFilter字段：= 1，_ 
 Criteria1：= Application.Transpose（FslList），_ 
运算符：= xlFilterValues 
 End Sub 
  

您将看到，即使 eee 在条件列表中，但不在列表中，需要进行过滤。

这是 Sheet5 在运行宏后过滤

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

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:

   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.

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:

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

The named range "PODCustList" is defined as follows:

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

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.

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

Thank you in advance,

解决方案

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.

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

See this example

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

Now select A1 in Sheet1 and give it a name, say, Criterias. Next create a name called FslList in the Name Manager and set the formula as =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$1").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 eee is there in the criteria list but not in the list that needs to be filtered.

This is how the Sheet5 gets filtered after you run the macro

这篇关于VBA自动过滤器使用数组 - 忽略条件，如果它不在过滤列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！