# Thread: Is there a #rows limit to pasting an Array to a Range? Thanks:  1 Post #5344515 (1) Likes: 0

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

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:
Code:
```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:
Code:
```    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 . .

2. ## Re: Is there a #rows limit to pasting an Array to a Range?

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

OK

Henk

4. ## Re: Is there a #rows limit to pasting an Array to a Range?

You're welcome