基于值列在Excel VBA中过滤表 [英] Filter table in excel VBA based on a column of values

问题描述

我有一张表，我想按其中一列的值过滤行.用于过滤的值存储在单独的列中，而不在表中.这是我到目前为止的内容:

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:=???

我不知道该为标准做些什么.我知道它必须是一个数组，我可以将其设置为类似Array("12"，"2"，"13")的东西，但是我需要使其等于在range1给定范围内指定的值.任何帮助将不胜感激.

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.

通过执行range1.Value，然后将Variant转换为字符串数组，我已经能够将范围值获取到Array中.这没有按我想要的方式工作，因为它只是将我的Filter设置为数组中的最后一个值.例如，如果我的数组包含ID("12"，"44"，"13"，"22")并且我将Criteria1设置为该数组并运行它，则过滤器仅选择了22个，所有其他数字都被取消选择，包括12、44和13.

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.

推荐答案

我知道了！我曾经尝试过重新编码，但是第一次尝试，由于与一行代码相关的行太多，它给我提供了一个不完整的程序.所以我重录了录音以提供完整的代码，结果发现我遗漏了一些东西.这是完整的代码:

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

"Operator:= xlFilterValues"是我第一次录制宏时错过的关键部分，因为录制过早停止了

the "Operator:=xlFilterValues" was the key part I missed from recording the macro the first time because the recording stopped pre-maturely

这篇关于基于值列在Excel VBA中过滤表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！