Excel slows down while using looping code

[liquid106]

Hi

I'm using the following code in an Excel 2003 (Windows XP) Macro to populate formulas down the rows of a spreadsheet a certain number of times (variable - numContacts). Everything works fine to begin with as the macro adds rows very rapidly, but after about 3000 lines, it starts to slow down. nunContacts is around the 22,000 figure so with this number of rows to add to two tabs on a report, it takes roughly 16 hours to finish the macro running.

I've found code that suggests saving the spreadsheet after a number of iterations but this doesn't appear to make the code run any faster after the file saves. I've also tried to add all the rows in one go, but I get the error '4008: Selection too large'.

Can anybody help me speed this up?

Cheers

S.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~
Application.Calculation = xlManual

For pasteLoop = 2 To numContacts

Range("A" & pasteLoop + 5 & ":DK" & pasteLoop + 5).Select

Selection.AutoFill Destination:=Range("A" & pasteLoop + 5 & ":DK" & pasteLoop + 6), Type:=xlFillDefault

Next pasteLoop

Application.Calculation = xlAutomatic
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~

[DeMan]

Hi liquid106,

Welcome to thscipts. This thread doesn't really fit the categories abvailable for forums (on the links from the blue bar at the top of the screen), so I will forward this on to the experts in "Miscellaneous Questions". Hopefully they can at least point you in the right direction!!

[liquid106]

Hi liquid106,

Welcome to thscipts. This thread doesn't really fit the categories abvailable for forums (on the links from the blue bar at the top of the screen), so I will forward this on to the experts in "Miscellaneous Questions". Hopefully they can at least point you in the right direction!!

Sorry about that, wasn't sure where to put it!

[OrangeHair]

Did anyone find an answer to this? I'm having a similar problem with one of my macros.

I had a macro that copied row 1 to 2, then 2 to 3, etc and it was taking way too long to complete. I then changed it to copy down all at once with no issues and much faster performance. That is until recently when I did a large data set with more rows and I received the "selection to large" error. The weird thing is that this macro does the same copy routine on several other tabs of my excel file with no problems. It only has a problem with this one tab. It doesn't matter if I re-order the macro to run the tabs in a different order, i.e. it always messes up on this tab.

This tab is slightly different from the others in that it is using an external reference to a *.dll that I made. Other than that it is an identical tab, copying the same sets of rows.

Any help would be appreciated.

[bartonc]

Did anyone find an answer to this? I'm having a similar problem with one of my macros.<snip>
This tab is slightly different from the others in that it is using an external reference to a *.dll that I made. Other than that it is an identical tab, copying the same sets of rows.

That's a great big "other than" in my view. I imagine that you tested your external function from outside the macro and it works (even on a large data set, as you describe). There are a lot of things to get right in order to make the external reference work and Excel is not a great tool.

Just my view,
Barton

[OrangeHair]

Yes it is a great big "other than". I probably should have mentioned that this copy down macro (which has the error) has nothing to do with the external *.dll. All this macro does is copy the rows down. The source cells have only standard excel formulas in them. A co-worker suggested that maybe one of the formulas was extrodinarily complicated (bunch of nested if statements) so I removed that but with no luck.

The external *.dll is used, manually, much later (and in a completely different macro). All the *.dll does is grab a big matrix, do some calculations, and return the same size matrix back.

Thanks for the response!