Is it possible to fill an array with row numbers which match a certain criteria without looping?

calendar_today Asked Oct 16, 2012
thumb_up 11 upvotes
history Updated April 16, 2026

Question posted 2012 · +11 upvotes

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

Accepted answer +11 upvotes

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

Excel VBA objects referenced (4)

Top excel-vba Q&A (6)

+11 upvotes ranks this answer #55 out of 136 excel-vba solutions on this site .
excel-vba