A loop for modifying and printing an array in VBA

Question

So, I'm trying to print modified iterations of an array of 100; for the first row I want 1 to 100 of the array, for the second 2 to 100, all the way to the 100th row with just array(100), and all of these rows starting with column A. I can print the first row just fine, but for the subsequent ones I'm not getting any output.

q = 1
For m = 1 To last_age
    Sheets("Sheet1").Range(Cells(q, 1), Cells(q, UBound(Data) + 1)) = Data 'Works the first pass, but not for q>1
    For p = 0 To UBound(Data) - 1
        Data(p) = Data(p + 1)
    Next p
    If UBound(Data) > 0 Then
        ReDim Data(0 To UBound(Data) - 1)
        q = q + 1
    End If
Next m

All my variables seem to be incrementing correctly, but after the first m loop my Data array isn't being put in the second row. Any thoughts?

Answer 1

You are re-dimensioning your array in this line:

ReDim Data(0 To UBound(Data) - 1)

but you are not using the Preserve keyword so your data is getting deleted. Try using:

ReDim Preserve Data(0 To UBound(Data) - 1)

Answer 2

Slightly different approach:

Sub Tester()

    Dim data(100), i, last_age, sht As Worksheet, q, m

    'fill test array
    For i = 1 To 100: data(i - 1) = i: Next

    Set sht = Sheets("Sheet1")
    last_age = 100

    q = 1
    sht.Cells(q, 1).Resize(1, UBound(data) + 1) = data

    For m = 2 To last_age

        q = q + 1
        sht.Cells(1, 1).Offset(0, m - 1).Resize( _
             1, (UBound(data) + 1) - (m - 1)).Copy _
             sht.Cells(q, 1)

    Next m


End Sub