Excel VBA自动过滤除三个以外的所有内容

Question

在我的数据分析的持续传奇中（第一个问题），我想删除部门（字段7）不是101、102或103（名称已更改以保护无辜）的所有行。数据中大约有一百个部门，因此使用Criteria1:=Array("104", "105", "106",etc是不切实际的。

我想做这样的事情：

myrange.AutoFilter Field:=7, Criteria1:="<>101", Operator:=xlOr, _

    Criteria2:="<>102", Operator:=xlOr, Criteria3:="<>103"

但Excel识别的标准不超过2个。我可以添加一个帮助器列，并使宏遍历每行（如果是101、102或103，则value = Yes），过滤出yes，然后删除所有剩余的内容，但我将其保存为最后采取。

有没有办法使自动筛选条件1不等于数组？就像是：

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

Answer 1

记住目标是删除不匹配的行；自动筛选只是帮助实现该目标的一种工具。如果自动筛选不能满足您的需求，请选择其他方法。考虑：Sub AllBut()&nbsp; &nbsp; Dim rTable As Range, r As Range&nbsp; &nbsp; Dim rDelete As Range&nbsp; &nbsp; Set rTable = Selection&nbsp; &nbsp; Set rDelete = Nothing&nbsp; &nbsp; For Each r In rTable.Columns(7).Cells&nbsp; &nbsp; &nbsp; &nbsp; v = r.Value&nbsp; &nbsp; &nbsp; &nbsp; If v <> "101" And v <> "102" And v <> "103" Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If rDelete Is Nothing Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set rDelete = r&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set rDelete = Union(r, rDelete)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp; Next&nbsp; &nbsp; If Not rDelete Is Nothing Then rDelete.EntireRow.DeleteEnd Sub在这里，我们选择要处理的数据块（不包括标题行）。宏向下扫描该块的第7列，并删除任何不符合条件的行。剩下的将是101、102和103。

Answer 2

由于这是关于AutoFilter方法的，因此我将提供这种方法，其中涉及使用Scripting.Dictionary对象来模拟在工作表上手动执行该过程。在工作表上，用户将应用“自动筛选”，然后使用G列的下拉菜单“关闭” 101、102和103值。剩下的将被删除。在VBA中，我们可以获取所有G列，并使用非101、102或103的值填充字典对象，并将其用作过滤操作的标准。Sub filterNotThree()&nbsp; &nbsp; Dim d As Long, dDELs As Object, vVALs As Variant&nbsp; &nbsp; Set dDELs = CreateObject("Scripting.Dictionary")&nbsp; &nbsp; With Worksheets("Sheet6")&nbsp; &nbsp; &nbsp; &nbsp; If .AutoFilterMode Then .AutoFilterMode = False&nbsp; &nbsp; &nbsp; &nbsp; With .Cells(1, 1).CurrentRegion&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'grab all of column G (minus the header) into a variant array&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vVALs = .Resize(.Rows.Count - 1, 1).Offset(1, 6).Value2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'populate the dictionary object with the values that are NOT 101, 102, or 103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; For d = LBound(vVALs, 1) To UBound(vVALs, 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Select Case vVALs(d, 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Case 101, 102, 103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'do not add&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Case Else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'not a match, add it to the delete list&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'the AutoFilter criteria needs to be text&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' so we set the Keys as text and the Items as numbers&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; dDELs.Item(CStr(vVALs(d, 1))) = vVALs(d, 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End Select&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Next d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'check to make sure there is something to filter on&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If CBool(dDELs.Count) Then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'filter on the dictionary keys&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .AutoFilter field:=7, Criteria1:=dDELs.keys, Operator:=xlFilterValues&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'delete the visible rows (there has to be some)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).EntireRow.Delete&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp; &nbsp; &nbsp; End With&nbsp; &nbsp; &nbsp; &nbsp; If .AutoFilterMode Then .AutoFilterMode = False&nbsp; &nbsp; End With&nbsp; &nbsp; dDELs.RemoveAll: Set dDELs = NothingEnd Sub

Answer 3

我在做类似的事情，但在两个领域，这种语法对我有用：myrange.AutoFilter Field:=7, Criteria1:="<>101", Operator:=xlAnd, Criteria2:="<>102", Operator:=xlAnd希望能帮助到你。