使用VBA自动筛选数组 - 无视标准，如果它不是在过滤列表 [英] VBA autofilter using an array - ignore criteria if it is not in the filtered list

问题描述

我一直在到处找解决这个VBA自动筛选的问题，任何想法都是AP preciated：

我有一个命名范围FslList的自动筛选标准的静态列表 - 我已经转换成一个阵列渔政船在数据工作表自动筛选列14：

 暗淡FSLArray为Variant
        昏暗rngFSL由于范围
        设置rngFSL = RawData.Worksheets（1）.Range（FslList）
        FSLArray = rngFSL.Value        与NewBook​​.Worksheets（1）
          .Cells.AutoFilter现场：= 14，标准1：= Application.Transpose（FSLArray），操作员：= xlFilterValues
 

在我过滤掉从数组中的值 - 我需要删除它们。

 随着.AutoFilter.Range
            设置DeleteRange = .Offset（1,0）.Resize（.Rows.Count  -  1）.SpecialCells（xlCellTypeVisible）
          结束与          DeleteRange.EntireRow.Delete
          NewBook​​.Worksheets（1）.AutoFilterMode =假
        结束与
 

我的问题是我的数据的列表被永诺变化的，而不是从FSLArray所有值都在列被过滤。因此，自动筛选停止，一旦它遇到的标准是不在名单上 - 并且不包括任何下列条件时，过滤

我想这样做是自动筛选，继续筛选使用其他阵列的标准，如果一个或多个数组中的元素，没有发现当中要过滤的数据。

编辑：我从号码更改的数据在我的数组（它是）字母 - 它正常工作与现在的字母

我曾尝试重新写code和定义一个名为范围的建议：

元素我有数组（范围C11：C14）是：

 加
9158
11958（这个人是不是在数据列表present）
15938
15940
 

命名范围PODCustList的定义如下：

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

在code是相同的：

 暗淡PODCustArray为Variant
昏暗rngPODCust由于范围
设置rngPODCust = RawData.Worksheets（1）.Range（PODCustList）
PODCustArray = rngPODCust.Value随着工作表（1）
  .Cells.AutoFilter现场：= 7，标准1：= Application.Transpose（PODCustArray），操作员：= xlFilterValues
 

我所得到的中筛选之后所得的回报是9158的元素在其中过滤。

仅行

解决：我需要通过它来运行我的数组 - 标准1：=斯普利特（加入（Application.Transpose（PODCustArray）））为自动筛选正确间preT的数据中作为一个字符串数组。

我能适应我的code，或者我需要使用不同的方法？

感谢你在前进，

解决方案

  

我的问题是我的数据的列表被永诺变化的，而不是从FSLArray所有值都在列被过滤。因此，自动筛选停止，一旦它遇到的标准是不在名单上 - 并且不包括任何下列条件时，过滤

这要看你怎么样定义你的范围（FslList）

请参阅此例中

我有其中有一个工作簿工作表Sheet1 和 Sheet5 。 工作表Sheet1 有列表和 Sheet5 具有需要过滤的数据。该工作簿可以从<一个下载href=\"https://skydrive.live.com/redir?resid=CDD3F8ABE20BBE3B!220&authkey=!AC_arVEF97AvCOk\">HERE

现在在工作表Sheet1 选择 A1 ，并给它一个名称，比如，规定 - 。接下来创建一个名为 FslList 在名称管理器名称，并设置公式 = OFFSET（指标分析，1,0，COUNTA（工作表Sheet1 $ A！ $ A）-1,1）

现在运行这个code

 显式的选项子样品（）
    昏暗FslList为Variant
    昏暗WS1作为工作表，WS2作为工作表
    昏暗rngCritList作为范围，rngSh5作为范围    设置WS1 =工作表（Sheet5）
    设置WS2 =工作表（工作表Sheet1）    设置rngSh5 = ws1.Range（$ A $ 1）。CurrentRegion
    设置rngCritList = ws2.Range（FslList）    FslList = rngCritList.Value    rngSh5.AutoFilter现场：= 1，_
                      标准1：= Application.Transpose（FslList），_
                      运营商：= xlFilterValues
结束小组
 

您将看到列表被过滤，即使 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屋！