Fill array with consecutive integers

Question

I'm trying to fill an array with consecutive integers using Excel and VBA to pass to the Subtotal function. I need a dynamically sized array of consecutive ints. The fnction works when I use Array(1,2,3,4,5) for example, but not if I try to use a dynamically sized array.

I've read some articles on this but none actually worked - has anyone else tried this?

Answer 1

Dont use dynamic arrays... Try to use an arraylist or something like that

Resizing them if full is a very expensive operation

If you know how many emelemts you need, size it right from the beginning.

Also totalling consecutive numbers is possible without the use of an array at all...

Answer 2

If you want the sum of the range from N to M, then use this formula:

(M * ((M + 1) / 2)) - ((N - 1) * (N / 2))

Answer 3

I assume you mean the Subtotal method. This works for me, you'll have to compare it to what you're doing.

Sub Macro1()

    Dim aConsec() As Long
    Dim i As Long

    ReDim aConsec(1 To 2)

    For i = 1 To 2
        aConsec(i) = i
    Next i

    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=aConsec, _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub