是否可以在不循环的情况下用符合特定条件的行号填充数组? [英] Is it possible to fill an array with row numbers which match a certain criteria without looping?

问题描述

我想用满足特定条件的行的行号填充 VBA 中的数组.我想要最快的方法(例如，类似 RowArray = index(valRange=valMatch).row)

I would like to fill an array in VBA with the row numbers of only rows which meet a certain criteria. I would like the fastest method possible (for example, something like RowArray = index(valRange=valMatch).row)

以下是(慢)范围循环的代码.

Below is the code for the (slow) range loop.

当前代码

Sub get_row_numbers()

Dim RowArray() As Long
Dim valRange As Range
Dim valMatch As String

Set valRange = ActiveSheet.Range("A1:A11")
valMatch = "aa"
ReDim RowArray(WorksheetFunction.CountIf(valRange, valMatch) - 1)

For Each c In valRange
    If c.Value = valMatch Then RowArray(x) = c.Row: x = x + 1
Next c    
End Sub

推荐答案

仍然是 Chris 的高效变体数组的 2-3 倍左右，但该技术非常强大，并且应用范围超出了这个问题

Still around 2-3 times the time of the efficient variant array from Chris, but the technique is powerful and has application beyond this question

需要注意的一点是，Application.Transpose 限制为 65536 个单元格，因此需要将更长的范围分块"成碎片.

One point to note is that Application.Transpose is limited to 65536 cells, so a longer range needs to be "chunked" into pieces.

Sub GetEm()
Dim x
x = Filter(Application.Transpose(Application.Evaluate("=IF(A1:A50000=""aa"",ROW(A1:a50000),""x"")")), "x", False)
End Sub

这篇关于是否可以在不循环的情况下用符合特定条件的行号填充数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！