Write Array Rows n to m to Sheet

[k64]

Hello,

I have an array that is >1 million rows. I'd like to write the first million rows to a sheet and then the next to another sheet. Is there a way to write an array to a worksheet, not starting at the first row? I know I could use loops to go through and put each million rows into a new array, but I'd like to avoid using extra memory if possible.

Thanks,
K64

[dflak]

I'll give this some thought. I think you'll be stuck using at least one loop, but pick up where you left off on each page. I'd like to see if there is a better way such as copying whole blocks out of the array.

[xlnitwit]

Hi,

How did the data get into the array in the first place? It might be easier to try and create several arrays, or an array of arrays, at that stage and then populate sheets using those. The only non-looping alternatives I am aware of for extracting sections of arrays are using Application.Index, which will not work with data of that size, or actually trying to copy blocks of memory using API calls.

[jindon]

If you are loading the data from the range, it should be less than 2^20 rows, so one way

Please Login or Register  to view this content.

[k64]

I am generating the data by unpivoting data that I have. So for example, I have 60,000 rows x 100 columns, that I am turning into 6 million rows and 1 column. I'd like to have something like

[Would put Code here but for some strange reason the site is blocking me from doing so]

In words, loop through the array, incrementing by 1 million and dumping every million rows onto a new sheet.

[dflak]

It's crude but it works.

The magic subroutine is PrintArrray and it is passed four arguments: SheetName, DimX, DimY and Pieces
SheetName is the root name of the output sheets. For example, "MyData" will produce MyData001, MyData002, etc.
DimX is the first dimension of the array
DimY is the second dimension of the array
Pieces is the number of pieces you want to divide the array into.

In the example I take a 100 x 3 Array and slice it into pieces of 33 rows each - so I wind up with three pages of 33 rows and one page of one row.

[k64]

Hmm, that's an interesting way to do it. It seems that it still takes N extra operations instead of N/1million operations. Maybe what I'm looking for just can't be done. I can paste the first X rows of an array in a single operation, but can't seem to figure out how to paste the next X rows as a block. I was hoping there was a way to trick excel using the resize, offset, or transpose functions. I'll leave this question open a little more to see if anything comes up, but if it's not possible, I'll have just have to run the program on a subset of my data or paste it in smaller blocks, looping through each element and clearing variables between blocks to preserve memory.

[dflak]

That's what I was looking for as well. I could not figure out a way to position a "pointer" inside the array and copy the next X elements.

[xlnitwit]

I am generating the data by unpivoting data that I have. So for example, I have 60,000 rows x 100 columns, that I am turning into 6 million rows and 1 column.

Why don't you output the initial array each time you reach a million rows and then start populating from the beginning again?

[k64]

dflak, I found one semi-solution but it seems more trouble than it is worth. If I defined my array as columns x rows instead of rows x columns, I could use either CopyMemory or Redim Preserve and paste the transpose. However, Redim preserve is expensive, CopyMemory can causes crashes if you're not careful and transpose may have row limits, so I think probably doing it in smaller chunks is better.

xlnitwit,
I thought about doing that after it seemed I couldn't paste array blocks. At first I thought it wouldn't work since for Pivoting, different rows combine into a single column, but thinking about it again, I realize that this could work. I am unpivoting, so I am splitting a single row into multiple, and while I couldn't stop at exactly 1million or 2^20, I could leave myself enough space to finish the current row and then do the cutoff and store the row number I was at in a variable. This might be what I do.

Everyone,
Thank you for your help. I'm going to mark this topic as solved. It is strange to me that there doesn't exist functionality for copying a block from an array to a range, except if that block starts at row 1, but I guess excel has all sorts of quirks. If any one you happen to come up with a way later, feel free to come back and let me know. Thanks!