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

#### 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

OK

Henk

You're welcome

1,084,744
Messages
5,379,581
Members
401,614
Latest member
priokatm

### This Week's Hot Topics

• VBA code giving errors and stopping Excel
Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
• Disable MsgBox message
Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
• Macro Recorder into VBA, Copy Paste Data Filled Cells
Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
• Number format changes while pasting into a cell
Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
• Collating data
Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
• Sum Multiple Columns Based on Multiple Criteria
I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...