vba 如何使用多个条件过滤掉VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45659661/
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
How to filter out VBA with multiple criteria
提问by DIVYA
My problem is that I want to filter out a specific value in different column of sheet, I tried record macro but the record feature seems to only support selecting specific values to keep instead of take out.
我的问题是我想过滤掉工作表不同列中的特定值，我尝试了记录宏，但记录功能似乎只支持选择要保留的特定值而不是取出。
For example, if the column is filled with weekly data's which is not known beforehand, I want to make sure a specific category is not in there. If a sheet was filled weekly data, with columns headings like Company, Product, Id,Date of manufacturing, manager name and all .
例如，如果该列填充了事先未知的每周数据，我想确保其中没有特定类别。如果工作表填写了每周数据，列标题为 Company、Product、Id、Date of Manufacturing、manager name 和 all。
From the column for company i want to take out "ABC company" ,”FAST ltd”,”WIN Company” without having to know the name of other companies in list. Also I want to take out some products like “computer”,'phone”,” laptop”,” sim”. The record feature in macro only seems to say what to keep, not what to take away . I would have to select every company except the 3 companies which I want to filter out.
从公司栏我想取出“ABC公司”，“FAST ltd”，“WIN Company”，而不必知道列表中其他公司的名称。另外我想拿出一些产品，比如“电脑”、“手机”、“笔记本电脑”、“sim”。宏中的记录功能似乎只说要保留什么，而不是要带走什么。除了要过滤掉的 3 家公司外，我必须选择所有公司。
Please help me to get the code for VBA for filter out using multiple criteria.
请帮助我获取 VBA 的代码，以便使用多个条件进行过滤。
I've tried this code
我试过这个代码
ActiveSheet.Range("$A:$G").AutoFilter Field:=2, Criteria1:="<>ABC ccompany", _
Operator:=xlAnd, Criteria2:="<>WIN company", Operator:=xlAnd, Criteria3:="<>FAST ltd"
But I am getting "run time error 1004".I think Criteria method allows only two criteria at a time.
但我收到“运行时错误 1004”。我认为 Criteria 方法一次只允许两个条件。
Any help would be great.
任何帮助都会很棒。
回答by Dirk Horsten
Solution hiding the lines themselves:
隐藏行本身的解决方案：
Option Explicit
Sub hide_values(Company As Variant, Product As Variant)
On Error GoTo hell
Dim myList As ListObject, myLine As Range, myValues As Variant
Set myList = Me.ListObjects(1) ' assuming you have just one table on your page
For Each myLine In myList.DataBodyRange.Rows
myValues = myLine.Value
myLine.EntireRow.Hidden = False
If myValues(1, 1) = Company Then
myLine.EntireRow.Hidden = True
End If
Else If myValues(1, 2) = Product Then
myLine.EntireRow.Hidden = True
End If
Next myLine
End
hell:
Debug.Assert False
Resume
End Sub
Sub test_it()
ActiveSheet.hide_values "FAST ltd", "computer"
End Sub
This sollution is incomplete because it only excludes one Company and one product, but it can be extended by passing a set of strings for each and looping through these sets.
这个解决方案是不完整的，因为它只排除了一个公司和一个产品，但可以通过为每个公司和一个产品传递一组字符串并循环遍历这些集合来扩展它。
回答by Mitch
As an alternative to AutoFilter, I found this from @Gary's Student to hide or show entire rows on the sheet. Very simple and easily modifiable.
作为 AutoFilter 的替代方法，我从 @Gary's Student 中找到了它来隐藏或显示工作表上的整行。非常简单且易于修改。
Sub ThreeWay()
Dim rng As Range, r As Range
Set rng = Range("A2:A25")
For Each r In rng
v = Left(r.Value, 1)
If v = "A" Or v = "D" Or v = "M" Then
r.EntireRow.Hidden = False
Else
r.EntireRow.Hidden = True
End If
Next r
End Sub
回答by Dirk Horsten
Solluction setting the filters
解决方案设置过滤器
Sub hide_values(Company As Variant, Product As Variant)
On Error GoTo hell
Dim myList As ListObject, myLine As Range, myValues As Variant
Set myList = Me.ListObjects(1) ' assuming you have just one table on your page
myList.Range.AutoFilter Field:=1, Criteria1:="<>" & Company
' , Operator:=xlAnd, Criteria2:="<"&OtherCompany
myList.Range.AutoFilter Field:=1, Criteria1:="<>" & Product
hell:
Debug.Assert False
Resume
End Sub
Sub test_it()
ActiveSheet.hide_values "FAST ltd", "computer"
End Sub
This sollution is incomplete because it only excludes one Company and one product. Extending it to exluding two porducts and companies is evident (see the commented out code), but extending it further is requires completely different code to set the filters. (Let me know if you need me to elaborate that.)
这个解决方案是不完整的，因为它只排除了一家公司和一种产品。将其扩展为排除两个产品和公司是显而易见的（请参阅注释掉的代码），但进一步扩展它需要完全不同的代码来设置过滤器。（如果您需要我详细说明，请告诉我。）
The advantage of this option is: the user keeps control over the selection after the subroutine ran.
此选项的优点是：用户在子程序运行后保持对选择的控制。
