Excel VBA 自动过滤除三个之外的所有选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19497659/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA autofilter all but three
提问by Kes Perron
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.
在我的数据分析（第一个问题）的持续传奇中，我想删除所有部门（字段 7）不是 101、102 或 103（名称已更改以保护无辜者）的行。数据中大约有一百个部门，所以使用Criteria1:=Array("104", "105", "106",etc是不切实际的。
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.
但 Excel 不能识别超过 2 个条件。我可以添加一个辅助列，并让宏在每一行中运行（如果是 101、102 或 103，则 value=Yes），过滤掉是，并删除所有剩余的，但我将其保存为最后采取。
Is there a way to Autofilter Criteria1 to be Not Equal To an array? Something like:
有没有办法让 Autofilter Criteria1 不等于数组？就像是：
myrange.AutoFilter Field:=7, Criteria1:="<>" & Array("101", "102", "103")
回答by Gary's Student
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:
记住目标是删除不匹配的行；AutoFilter 只是帮助实现目标的一种工具。如果 AutoFilter 不能满足您的需求，请选择其他方法。考虑：
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.
这里我们选择要处理的数据块（不包括标题行）。该宏向下扫描该块的第 7 列并删除任何不符合条件的行。
All that will remain are the 101's, the 102's, and the 103's.
剩下的将是 101、102 和 103。
回答by Gary's Student
Since this was about the AutoFilter method, I'll offer up this method involving using a Scripting.Dictionary objectto mimic the procedure that would be used if this was manually performed on the worksheet.
由于这是关于AutoFilter 方法，我将提供此方法，涉及使用Scripting.Dictionary 对象来模拟如果在工作表上手动执行此操作将使用的过程。
On the worksheet, the user would apply an AutoFilter then use column G's drop down to 'turn off' the 101, 102 and 103 values. What remained would be be deleted. In VBA, we can grab all of column G and populate a dictionary object with the values that are not 101, 102 or 103 and use that as the criteria for the filter operation.
在工作表上，用户将应用自动筛选，然后使用 G 列的下拉菜单“关闭”101、102 和 103 值。剩下的将被删除。在 VBA 中，我们可以获取 G 列的所有内容并使用不是 101、102 或 103 的值填充字典对象，并将其用作过滤操作的标准。
Sub filterNotThree()
Dim d As Long, dDELs As Object, vVALs As Variant
Set dDELs = CreateObject("Scripting.Dictionary")
With Worksheets("Sheet6")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
'grab all of column G (minus the header) into a variant array
vVALs = .Resize(.Rows.Count - 1, 1).Offset(1, 6).Value2
'populate the dictionary object with the values that are NOT 101, 102, or 103
For d = LBound(vVALs, 1) To UBound(vVALs, 1)
Select Case vVALs(d, 1)
Case 101, 102, 103
'do not add
Case Else
'not a match, add it to the delete list
'the AutoFilter criteria needs to be text
' so we set the Keys as text and the Items as numbers
dDELs.Item(CStr(vVALs(d, 1))) = vVALs(d, 1)
End Select
Next d
'check to make sure there is something to filter on
If CBool(dDELs.Count) Then
'filter on the dictionary keys
.AutoFilter field:=7, Criteria1:=dDELs.keys, Operator:=xlFilterValues
'delete the visible rows (there has to be some)
.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).EntireRow.Delete
End If
End With
If .AutoFilterMode Then .AutoFilterMode = False
End With
dDELs.RemoveAll: Set dDELs = Nothing
End Sub
????????
????????????????Data before filterNotThree sub procedure
????????? ?????????????????? filterNotThree 子程序之前的数据
????????
????????????????Data after filterNotThree sub procedure
????????? ?????????????????? filterNotThree 子程序后的数据
回答by AniSetchi
I was doing something similar but for two fields and this syntax worked for me:
我正在做类似的事情，但对于两个领域，这种语法对我有用：
myrange.AutoFilter Field:=7, Criteria1:="<>101", Operator:=xlAnd, Criteria2:="<>102", Operator:=xlAnd
Hope it helps.
希望能帮助到你。
