Is there a #rows limit to pasting an Array to a Range?

[Repush]

Hi, All.

I am processing some data (1 year, 5 min. average, so 105120 rows) of several data-items.
Col A: Time
Col B: data
Col C: data
. . .

First the values are stored in 1-D arrays, then some data cleaning is done, then each array is pasted to an Excel-sheet.
ArrPI: Variant/Variant(1 to 105120, 1 to 3)
ArrTime: Variant/Variant(1 to 105120)
ArrVal: Variant/Variant(1 to 105120)
Code snippet:

Sub Get_Data()
    Dim ArrPI As Variant
    Dim ArrTime As Variant, ArrVal As Variant
    ' preprocessing
    ' .
    ' .
    ArrPI = Application.Run( . . . .) 'results in a 2D array
    ArrTime = CleanArray(GetArrayCol(ArrPI, 1)) ' "Column" 1
    ArrVal = CleanArray(GetArrayCol(ArrPI, 2))  ' "Column" 2
    ' result: all rows contain valid data
    nRows = UBound(ArrTime)
    ' Paste each Data colummn to Range
    Data.Resize(nRows, 1) = Application.Transpose(ArrTime)
    Data.Offset(0, 1).Resize(nRows, 1) = Application.Transpose(ArrVal)
    ' And so on . . .
End Sub

A month's data is no problem, but when the number of rows reaches 39584 only #N/A's appear.
My workaround now is:

    Dim i As Long
    For i = 1 To UBound(Arr)
        MyRange.Offset(i - 1, 0) = Arr(i)
    Next i

Q: is there a #rows limit to pasting an Array to a Range?
I prefer pasting in one go . .

 

[Fluff]

There is no limit to posting an array to a sheet, the limit is in the Transpose function.
Various options
1) clean the ArrPI and leave it as a 2d array, then post that to the sheet.
2) loop through the two 1d arrays to create a 2d array
3) Use your current workround.

 

[Repush]

OK
Thanks for your reply Fluff!

Henk

 

[Fluff]

You're welcome