Speed of Insert Copied Cells (entire row)

[Guest]

I have a large spreadsheet (>60MB with .xls) with 13 worksheets and LOTS of
formula's. One particular worksheet has 234 columns by 7184 rows. In that
worksheet, if I copy a few lines from another spreadsheet window in the same
Excel application and try to "Insert->Insert Copied Cells", it can take about
5 minutes for the lines to be inserted. Even with manual calculate turned on.

My question is whether there are a few tricks or tips for how to speed this
up. I realize that a spreadsheet this large with this much data is going to
take awhile to execute changes like these, but I'm curious if there's
anything I can do programmatically, procedurely or financially (spend $$) to
speed it up.

I notice, for example, that my cpu meter is at 100% for both processors when
I do a re-calculate (thus cpu-limited), but both cpu's and my memory are at
~50% when I do an Insert Copied Cells. So what is it doing the other 50% of
the time? Is this disk access speed limited?

I also notice I am running in "Compatibility Mode". Will this affect my
performance?

Here's my computer specs:
Software:
Vista OS
Office 2007

Hardware:
HP Pavilion m7750n
CPU - Athlon 64 X2 (W) 5000+ 2.6 GHz
Memory - 2GB
Disk - 400GB SATA (7200 RPM)

I do notice by the way that I have the same issue (though even slower) with
my 2.0 GHZ Pentium 4 with 1.5GB of memory laptop running Windows XP and
Office 2007.

Thank you in advance for your help!

 

[Guest]

I've found that if I turn off calculation while I'm updating cells and turn
it back on when I'm done (while including the appropriate calculation in the
code), it runs much faster.

I've also found that disabling screenupdating during execution of the code
helps too.
Do this with "Application.screenupating =FALSE" and turn back on with
"Application.screenupdating = true"

 

[Charles Williams]

When you insert lines you make Excel check & change formulae because their
references have changed.
If you have a very large number of formulae this will take a very long time.

The only solution I know of is to add the lines at the bottom rather than
insert them in the middle.
And make sure that your data is on a separate sheet to your formulae.

Compatibility mode does not affect performance as far as I can tell.

The only part of Excel that is multithreaded is the calculation engine
(AFAIK), so Excel will not use both CPUs when changing references.

Check out my paper on Improving Excel Performance at
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

it has a lot of tips for Excel 2007 as well as previous versions.
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com