Print to worksheet and Jagged Array

[abousetta]

I am working with a large file, calculating and printing the results to an array. Once all the calculations are done I print the results to the worksheet. Now the end result may take up more than the 56K limit for using transpose so now I am stuck with only a few options:

1) Redim the 2nd dimension (e.g. 1 to 5, 1 to 100000) and then use a loop to exchange all the first dimension elements with the second dimension elements thereby transposing the array

2) Starting off with a huge array from the start (e.g. 1 to 100000, 1 to 5) which is inefficient

I have thought that it might be possible to create a jagged array with each element consisting of two-dimensional arrays (1 to 5, 1 to 56000) and then internally transpose to print to sheet.

This is all theoretical and it might not be possible to redim a jagged array or transpose an element with a jagged array.

If anyone has knowledge or experience with this or another workaround, please let me know.

Thanks.

abousetta

[jindon]

I would dimention the array like

ReDim myArray(1 to Rows.count, 1 To 5)

So that you don't need to transpose.

If the result reaches to the limit, print the result to the sheet and ReDim (empty the array) and do the same thing again.

My small PC is capable to do this.
e.g

Sub test()
    Dim a, i As Long, ii As Long, n As Long, t As Long
    ReDim a(1 To Rows.Count, 1 To 5)
    t = 1
    For i = 1 To UBound(a, 1) * 2
        n = n + 1
        For ii = 1 To UBound(a, 2)
            a(n, ii) = Rnd
        Next
        If n = UBound(a, 1) Then
            Cells(1, t).Resize(n, UBound(a, 2)).Value = a
            ReDim a(1 To UBound(a, 1), 1 To UBound(a, 2))
            t = t + UBound(a, 2)
            n = 0
        End If
    Next
    If n > 0 Then
        Range("a1").Resize(n, UBound(a, 2)).Value = a
    End If
End Sub

[abousetta]

Hi jindon,

Thank you for the example. I have always assumed that the amount of memory needed to contain 5 million element like you demonstrated would be huge, but seems that I was mistaken. Thank you for showing me this approach. I will test it on my next analysis run. In the past, I have just limited the outcome by putting limitations on the number of results I would accept from the run, but now I just by seeing your example in action, I will try to push my computer's limits.

abousetta