vba 基于一列值的excel VBA中的过滤表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15977684/
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 table in excel VBA based on a column of values
提问by user972276
I have a table and I would like to filter the rows by the values in one of its columns. The values used to filter are stored in a separate column not in the table. This is what I have so far:
我有一个表格，我想按其中一列中的值过滤行。用于过滤的值存储在不在表中的单独列中。这是我到目前为止：
Dim table1 As ListObject
Dim range1 As Range
Set range1 = ActiveSheet.range("AM23:AM184")
'get table object
table1.range.AutoFilter Field:=3, Criteria1:=???
I do not know what to put for criteria1. I know it needs to be an Array and I can set it to something like Array("12","2","13") but what I need it to equal is the values specified in the range given by range1. Any help would be greatly appreciated.
我不知道应该为标准 1 设置什么。我知道它需要是一个数组，我可以将它设置为 Array("12","2","13") 之类的东西，但我需要它等于 range1 给定的范围内指定的值。任何帮助将不胜感激。
EDIT: I have been able to get my range values into an Array by doing range1.Value and then converting the Variant into a string array. This did not work as I wanted it to as it just sets my Filter to the last value in my array. For instance, if my array contains the IDs ("12","44","13","22") and I set Criteria1 to that array and run it, the filter only has 22 selected and all other numbers are deselected including 12, 44, and 13.
编辑：通过执行 range1.Value 然后将 Variant 转换为字符串数组，我已经能够将我的范围值放入一个数组中。这并没有像我想要的那样工作，因为它只是将我的 Filter 设置为我数组中的最后一个值。例如，如果我的数组包含 ID（“12”、“44”、“13”、“22”）并且我将 Criteria1 设置为该数组并运行它，则过滤器仅选择了 22 个，并且取消选择了所有其他数字，包括12、44 和 13。
回答by user972276
I figured it out! I had tried recoding but the first time I tried it, it gave me an incomplete program due to too many lines associated with one line of code. So I redid the recording to give me the whole code and turns out I was missing something. Here is the whole code:
我想到了！我曾尝试重新编码，但第一次尝试时，由于与一行代码相关联的行太多，它给了我一个不完整的程序。所以我重新录制了完整的代码，结果我遗漏了一些东西。这是整个代码：
Dim range1 As range
Set range1 = ActiveSheet.range("AM23:AM184")
Dim var1 As Variant
Dim sArray() As String
Dim i As Long
var1 = range1.Value
ReDim sArray(1 To UBound(var1))
For i = 1 To (UBound(var1))
sArray(i) = var1(i, 1)
Next
ActiveSheet.ListObjects("Table1").range.AutoFilter Field:=3, Criteria1:=sArray, Operator:=xlFilterValues
the "Operator:=xlFilterValues" was the key part I missed from recording the macro the first time because the recording stopped pre-maturely
“Operator:=xlFilterValues”是我第一次录制宏时错过的关键部分，因为录制过早停止
