vba 过滤数字字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20375818/
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
Filter numeric field
提问by adp
I have the following code:
我有以下代码：
ActiveSheet.Range("$A:$P1").AutoFilter Field:=5, Criteria1:="=10"*
When I click on the filter and type in the search bar 10*I get all results that start with 10. When using the macro, that doesn't work. The goal is for the macro to filter using the first two numbers provided by me.
当我点击过滤器并在搜索栏中10*输入时，我会得到所有以10. 使用宏时，这不起作用。目标是让宏使用我提供的前两个数字进行过滤。
Can you assist?
你能帮忙吗？
回答by Gary's Student
The following will work only if the values are text:
以下仅当值为 text 时才有效：
Sub Macro2()
ActiveSheet.Range("$A:$P1").AutoFilter Field:=5, Criteria1:="=10*", _
Operator:=xlAnd
End Sub
If the values are not text, then use a "helper" column.
如果值不是文本，则使用“helper”列。
EDIT#1:
编辑#1：
For postal codes in column E, this will filter out (hide) rows not containing "10*" codes:
对于 E 列中的邮政编码，这将过滤掉（隐藏）不包含“10*”代码的行：
Sub GoingPostal()
Dim r As Range
For Each r In Range("E2:E201")
st = Left(r.Text, 2)
If st <> "10" Then
r.EntireRow.Hidden = True
End If
Next r
End Sub
回答by pnuts
The core of the problem seems to have been trying to apply a text filter to a numeric field. Instead of:
问题的核心似乎是试图将文本过滤器应用于数字字段。代替：
ActiveSheet.Range("$A:$P1").AutoFilter Field:=5 ActiveSheet.Range("$A:$P1").AutoFilter Field:=5, Criteria1:="=10"*
just:
只是：
ActiveSheet.Range("$A:$P1").AutoFilter Field:=5, _
Criteria1:=">=10000", Operator:=xlAnd, Criteria2:="<=10999"
seems to have worked.
似乎奏效了。
