CODEWITHSUNDEEP
excelvba
Rene Hanschke
Rene Hanschke

Reputation: 57

Array of row numbers meeting a criteria


I need to create an array consisting of row numbers that meet a specific criteria.
Let's say I got a list of dates:

01.03.2019
01.07.2019
01.03.2020
30.03.2019
01.03.2019
01.06.2019

Now, assuming I need the row numbers of 01.03.2019, my array would be [1,5].
Is there any decent method to do so? My list has many thousand entries, so I suppose looping through each cell would take a while. Any lean solution would be much appreciated.
I found this thread, but the answers are not yet validated: Is it possible to fill an array with row numbers which match a certain criteria without looping?

Upvotes: 0

Views: 193

Answers (2)

user10862412
user10862412

Reputation:

It doesn't 'take a while' if you work from an array instead of the worksheet.

dim dt as long, i as long, j as long, tmp as variant, arr as variant

dt = dateserial(2019, 1, 3)

with worksheets("sheet1")

    tmp = .range(.cells(1, "A"), .cells(.rows.count, "A").end(xlup)).value2

end with

redim arr(0)

for i=lbound(tmp, 1) to ubound(tmp, 1)
    if tmp(i, 1) = dt then
        redim preserve arr(j)
        arr(j) = i   'collect row numbers
        j=j+1
    end if
next i

for i=lbound(arr) to ubound(arr)
    debug.print arr(i)  'print row numbers
next i

Upvotes: 2

dwirony
dwirony

Reputation: 5450

If your values were in column A, this would fill an array with the matching row numbers and print them to the immediate window:

Sub FillArray()

Dim myarray As Variant
Dim i As Long

ReDim myarray(0 To 0)

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1).Value = "01.03.2019" Then 'Value to find
        ReDim Preserve myarray(0 To UBound(myarray) + 1)
        myarray(UBound(myarray) - 1) = i
    End If
Next i

For i = 0 To UBound(myarray)
    Debug.Print myarray(i)
Next i

End Sub

Upvotes: 0

Related Questions