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

Solution 1:

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

Solution 2:

First copy the range to a variant array , then loop over the array

Arr = rngval
For I = 1 to ubound(arr)
    If arr(I,1) = valMatch Then RowArray(x) = I: x = x + 1
Next

Solution 3:

There is an assumption in the question title: that a looping solution is slow and a non-looping solution is faster. So, I conducted some comparisons to check that.

Test Case

I created some sample data consisting of 50,000 samples, and 50% matching values. For the fastest methods I created two more sample sets, again with 50,000 rows and one with 10% matching rows, another with 90% matching row.

I ran each of the posted methods over this data in a loop, repeating the logic 10 times (so times are for processing a total of 500,000 rows).

                  50%        10%        90%  
ExactaBox        1300       1240       1350  ms
Scott Holtzman 415000         
John Bustos     12500       
Chris neilsen     310        310        310
Brettdj           970        970        970
OP               1530       1320       1700

So the moral is clear: just because it includes a loop, doesn't make it slow. What is slow is access the worksheet, so you should make every effort to minimise that.

Update Added test of Brettdj's comment: single line of code

For completeness sake, here's my solution

Sub GetRows()
    Dim valMatch As String
    Dim rData As Range
    Dim a() As Long, z As Variant
    Dim x As Long, i As Long
    Dim sCompare As String

    Set rData = Range("A1:A50000")
    z = rData
    ReDim a(1 To UBound(z, 1))
    x = 1
    sCompare = "aa"
    For i = 1 To UBound(z)
        If z(i, 1) = sCompare Then a(x) = i: x = x + 1
    Next
    ReDim Preserve a(1 To x - 1)    
End Sub