Speeding up writes to cells?

[Bruce E. Stemplewski]

Is there anyway to speed up writing to cells? Seems a lot of updating of
the spreadsheet is going on while calling r.Cells(y, 1) = "XYZ".

Bruce E. Stemplewski
www.stempsoft.com

 

[Tom Ogilvy]

Set calculation to manual at the beginning of your macro and back to
automatic at the end.

 

[Myrna Larson]

Update the worksheet in batches by buffering the data in an array, then

Range("A1").Resize(100,5).Value = MyArray()

 

[Bruce E. Stemplewski]

Set calculation to manual at the beginning of your macro and back to
automatic at the end.

Thanks Tom,


I tried that and it did not seem to make much of a difference.

But just tried it again and I can see that it does make a little
difference just not huge. I am writing about 10000 rows X 8 columns so
that is a lot of cells.

Any other performance ideas?

 

[Bruce E. Stemplewski]

Update the worksheet in batches by buffering the data in an array, then

Range("A1").Resize(100,5).Value = MyArray()


On Sat, 29 Jan 2005 20:34:13 -0500, "Bruce E. Stemplewski "

Thank you Myrna,

I really can't do that since all of my elements might not always be in
the same row or the same order. My code allows for the end user to
specify which columns will be populated by the data. The columns can be
in any order, not next to one another or even on different sheets(why
someone would want to do that, I do not know, but it can be done).

But just for educational purposes, is that an array of variants I assume?



Bruce E. Stemplewski
GarXface OCX and C++ Class Library for the Garmin GPS
www.stempsoft.com

 

[Tom Ogilvy]

It can be any type of 2D array. If you have different data types in the
array, then it would probably need to be a variant array. But if the it
were all numbers, it could be double. The concept is still sound even if a
single array is not appropriate. If you are writing large blocks of data it
is faster to populate an array and put it down in one statement than writing
each individual cell.

 

[Myrna Larson]

Then, if you can't change the layout, you are probably stuck with poor
performance. Transferring data between the worksheet's data space and VBA's
data space is a BIG bottleneck, with writing being about 5-6 times slower than
reading.

I remember one instance in which a friend had to write lines of 40 elements.
He was doing this cell-by-cell, i.e. 40 writes per row. The macro took 2 HOURS
to run. When he simply buffered the data to write all 40 cells at once, the
time was reduced to 4 MINUTES.

 

[Myrna Larson]

BTW, you DO have Application.Screenupdating set to False, don't you?