如何使用数组自动过滤条件 [英] How do I autofilter using an array for criteria

问题描述

我只是在学习如何使用数组，并且对如何使用数组作为自动过滤条件的输入有些困惑.我想设置一个带有值的数组，然后使用这些相同的值过滤一个Excel电子表格.我做了下面的代码，但是当我尝试自动过滤时，它总是抛出错误

I am just learning about using arrays and am a bit stuck on how to use arrays as an input for criteria in autofiltering. I would like to set an array with values and then filter an excel spreadsheet using those same values. I did the below code, but it keeps throwing up an error when I try to autofilter

这是我的代码

Dim I As Integer
ReDim arr(1 to var) As Variant 'var is defined in a different function with a #
I = 1
For Each rngValue In rngValues.cells 'rngValues is defined in a different function
    If rngValue ="c" then
       arr(I)=rngValue.Offset(0,2)
    End If
    I = I +1
Next rngValue

arr(I) = "="

With ws1
    .[A1].Autofilter Field:=1, Criteria1:=arr, operator:xlfiltervalues
End With

推荐答案

您可以像下面那样直接传递Range值，从而更快地将Range值传递给数组:

You can pass Range value to array faster by directly passing it like below:

Dim arr As Variant '~~> no need to re-dimension
arr = Application.Transpose(rngValues) '~~> Transpose produces 1D array
ws1.Range("A1").AutoFilter 1, arr, xlFilterValues

请注意， rngValue 应该仅包含一维范围区域.
但是，如果您想坚持自己的逻辑；也可以处理2维范围或非连续范围，下面应该可以:

Note that rngValue should contain one dimension Range area only.
If however you want to stick with your logic; also to handle 2-dimension Range or non contiguous ranges, below should work:

Dim i As Long: i = 1
ReDim arr(1 to rngValues.Cells.Count)
For Each rngValue In rngValues
    arr(i) = rngValue.Value
    i = i + 1
Next
ws1.Range("A1").AutoFilter 1, arr, xlFilterValues

在任何情况下，请确保生成的用作过滤器的数组是一维的.

In any of the scenarios, make sure that the array produced to be used as filter is 1D.

这篇关于如何使用数组自动过滤条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！