是否有可能以填补符合特定标准，不用循环而行号的数组？ [英] Is it possible to fill an array with row numbers which match a certain criteria without looping?

问题描述

我想填补VBA数组仅符合一定条件的行的行号。我想最快速的方法（例如，像 RowArray =指数（valRange = valMatch）.row ）

下面是code为（慢）范围内循环。

当前code

 子get_row_numbers（）昏暗RowArray（）只要
昏暗valRange由于范围
昏暗valMatch作为字符串设置valRange = ActiveSheet.Range（A1：A11）
valMatch =AA
使用ReDim RowArray（WorksheetFunction.CountIf（valRange，valMatch） -  1）对于每个c在valRange
    如果c.Value = valMatch然后RowArray（X）= c.Row：X = X + 1
下一个C
结束小组
 

解决方案

从克里斯高效变量数组，但该技术的静止约2-3倍的时间是强大的，具有推广应用超出了这个问题。

要注意的一点是， Application.Transpose 被限制在65536细胞，使更大的范围需要分块成片。

 子GetEm（）
昏暗的点¯x
X =筛选（Application.Transpose（Application.Evaluate（= IF（A1：A50000 =aa的，ROW（A1：a50000），×，））），×，假）
结束小组
 

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.

Current Code

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

解决方案

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

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屋！