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

#### Repush

##### Board Regular
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 . .

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
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

• Repush

OK

Henk

You're welcome