Results 1 to 4 of 4

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

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Location
    the Netherlands
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,149
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default 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
    3) Use your current workround.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Sep 2015
    Location
    the Netherlands
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    OK
    Thanks for your reply Fluff!

    Henk

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,149
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •