Excel VBA自动过滤器除了三个 [英] Excel VBA autofilter all but three

问题描述

在我的数据分析（第一个问题），我想删除其部门（字段7）不是101,102或103（名称已更改以保护无辜者）的所有行。数据中有大约百个部门，所以使用 Criteria1：= Array（104，105，106，等等是不切实际的。

我想做这样的事情：

myrange.AutoFilter字段：= 7，条件1 ：=101，运算符：= x1Or，Criteria2：=102，运算符：= x10，Criteria3：=103 p>

但Excel不能识别超过2条标准，我可以添加一个帮助列，并且宏遍历每一行（如果101,102或103，则值=是），过滤掉yeses，并删除所有剩下的，但是我将其保存为最后的手段。

有没有办法将Autofilter Criteria1不等于数组？有些东西就像：

myrange.AutoFilter字段：= 7，Criteria1：=&& Array （101，102，103）

解决方案

删除不匹配的行; AutoFilter只是一个帮助实现的工具如果AutoFilter不符合您的需求，请选择另一种方法。考虑：

  Sub AllBut（）
 Dim rTable As Range，r As Range 
 Dim rDelete As Range 
设置rTable =选择
设置rDelete = Nothing 
对于每个r在rTable.Columns（7）中.Cells 
v = r.Value 
如果v < 101和v- 102和v- 103然后
如果rDelete不是，然后
设置rDelete = r 
 Else 
设置rDelete = Union（r，rDelete）
结束如果
结束如果
下一个
 
如果没有rDelete是没有，然后rDelete.EntireRow.Delete 
结束Sub 
  

这里我们选择要处理的数据块（不包括标题行）。宏扫描该块的第7列，并删除任何不符合条件的行。

所有将保留的是101的，102的和103的。

In the continuing saga of my data analysis (First Question), I want to delete all the rows whose departments (Field 7) are not 101, 102 or 103 (the names have been changed to protect the innocent). There are about a hundred departments in the data, so using Criteria1:=Array("104", "105", "106", etc is impractical.

I would like to do something like this:

myrange.AutoFilter Field:=7, Criteria1:="<>101", Operator:=xlOr, Criteria2:="<>102", Operator:=xlOr, Criteria3:="<>103"

but Excel doesn't recognize more than 2 Criteria. I could add a helper column, and have the macro run through each line (if 101, 102, or 103, then value=Yes), filter out the yeses, and delete all that remain, but I'm saving that as a last resort.

Is there a way to Autofilter Criteria1 to be Not Equal To an array? Something like:

myrange.AutoFilter Field:=7, Criteria1:="<>" & Array("101", "102", "103")

解决方案

Remember the goal is to delete the non-matching rows; AutoFilter is only one tool to help achieve the goal. If AutoFilter does not meet your needs, pick another method. Consider:

Sub AllBut()
    Dim rTable As Range, r As Range
    Dim rDelete As Range
    Set rTable = Selection
    Set rDelete = Nothing
    For Each r In rTable.Columns(7).Cells
        v = r.Value
        If v <> "101" And v <> "102" And v <> "103" Then
            If rDelete Is Nothing Then
                Set rDelete = r
            Else
                Set rDelete = Union(r, rDelete)
            End If
        End If
    Next

    If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

Here we select the block of data to be processed (not including the header row). The macro sweeps down column #7 of that block and deletes any row that does not match the criteria.

All that will remain are the 101's, the 102's, and the 103's.

这篇关于Excel VBA自动过滤器除了三个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！