Most Efficient Way to Write Out Data

I am reading in some large chunks of data and storing them in arrays or a multi-dimensional array.

What is the most efficient way to write out/append that data into a table? Looping and adding rows and then equating individual cells seems very slow to me.

How can we embed an array onto a spreadsheet using vba? In other words converting an array into a range?

(I used to work with Excel using C#, and there was a method which helped converting an array into a range which could be added onto the spreadsheet but I can't seem to find a similar method in vba.)

Many thanks for your help,

Hi ld_pvl,
one way would be to use resize

Please Login or Register  to view this content.

Most of the time the boundaries of an Array are variable to some extent so using the UBound properties of the Array for the Resize is often logical, however, note:

a) not all arrays are 2 dimensional
(for 1d Arrays you might also be looking to Transpose)

b) Option Base can vary so for a dynamic piece of code you might establish the resize of a given dimension using:

Please Login or Register  to view this content.

Thanks very much for your both replies.

Maybe I'm missing something, but one problem with '.Resize' that I found is that it doesn't add new rows into a table if the resize area is within the table area.

What I mean is this: Assuming I have a 2x2 table that like in column A and B, and it has 5 rows. If I run the following sub, I would expect the range 'a3:b3' to be resized into a twice as big range, or in other words extended, and therefore make the table to have 6 rows.

To me what '.resize' seems to do is overlap the below adjacent rows.

But yes, if the resize size is bigger than the table size than new rows and/or columns will be added into the table.

Please Login or Register  to view this content.

Back to my original task. I'm reading in large chunks of data and I need to append that data somewhere into within another table, the way I do it is shown in the following code and would like some opinions on it because I found a few drawbacks using this method:

Please Login or Register  to view this content.

The drawbacks:
- This doesn't work if the 'initialDataRow' (the row below which we want to add more data) is the last row. I worked around it by writing a special clause which peforms '.listrow.add' first and then do the rest.
- The way I add empty rows by doing '.Range.EntireRow.Insert' inserts new rows across the whole spreadsheet which might not be desirable in most cases when there are other things in the same sheet. Is there a way to add multiple rows into a table in one go that only affects that table?

I've added a working sample with the above two subs.

Many thanks for your help

Please Login or Register  to view this content.

From my perspective:

-- use Longs rather than Integers (esp. for row related variables)
-- try to declare variables together @ head of routine

Please Login or Register  to view this content.

there are shorter methods using Evaluate techniques with Variant arrays etc but it's imperative to use code that makes sense to you (and anyone else who will review it).
It is important you be able to work your way through it when coming back to in n months time.

Thanks. So there's no other way of inserting multiple table rows at once instead of using '.entirerow.insert'?

PS: Very nifty - I didn't realize that if you choose a cell right underneath the table and enter a value, that cell's row becomes part of the table (I usually just use 'tab' to append a new row). Your method exploited this incredibly.

If you want to insert rows into the Table object only then you would use the appropriate ListObject and ListRows.Add - then add the values.

You mention that amending the ListObject is slow - are you toggling Events, Calculation etc as part of this process ?

see: http://blogs.msdn.com/excel/archive/...practices.aspx

Originally Posted by ld_pvl

...what I have been trying to find is a method to add multiple rows into the Table object only.

As far as I know the above is not possible - ie iterative process required to insert Table Rows (only into the Table and not to the Parent Sheet object).

The other alternative would be to Unlist, treat as a range, recreate Table but that's risky in itself and hardly ideal.

I suspect with some optimisation (per the link) you "should" find things work relatively quickly.

Originally Posted by ld_pvl

Interesting blog entry. "Integers get converted to Longs anyway" - now I know why you said I should use Longs.

That's not the only reason (but it is for that reason I only ever really use Byte & Long)
There is a specific issue using Integer for Row variables, put simply the Row boundaries (any version) exceed that of the Integer type so you leave yourself open to Overflow errors otherwise.

Originally Posted by ld_pvl

I have attached a sample with the two new subs for comparison.

Not sure what I'm meant to be looking at re: the separate routines to be honest... both run instantaneously.

Originally Posted by D.O

... are you toggling Events, Calculation etc as part of this process ?

Originally Posted by ld_pvl

Nope. Basically what I have been trying to do for efficiency is pretty much of the same line that is mentioned in the blog you linked.

- Read in the data straight from the table at once, put into an array
- Insert multiple empty rows at once
- Write out all values in the array into those empty rows at once
- No sheet activation, no copy paste, no cell activation.

Yes, the point I was making was in relation to the ListRows.Add approach (rather than your use of Arrays)
This iterative process of row addition to the table could in theory invoke calculation, event driven code and repaint with each row insertion.
Toggling all of the above (per the blog - and as implemented in Insert100RowsUsingForLoop routine) should in theory aid performance.

What I would say is that wherever you toggle these settings you should add an appropriate handler such that all settings are returned to their cached states.

I'm afraid other than the above and prior suggestions, based on the specific requirements, I confess I don't have much more to offer.
That's not to say others won't.

edit:

another option would be to create the range of revised values (old-new-old) in a new sheet and copy & paste onto the table object - the rows at the bottom will be added automatically
whether that's viable depends on whether or not you have anything below the table object which needs to be moved physically beforehand (so as not to be overwritten)