Two Workbooks, Same VBA Code, One fast, One SLOW

[SusaninHouston]

That's it in a nutshell -- I have two nearly identical worksheets, both approximately 6 MB in size. The two worksheets are nearly identical and are both running the same VBA code which works very quickly in one workbook and VERY SLOWLY in the other. Processing Speed is about 1 seconds in the fast model vrs 3 minutes in the slow.

What I've already checked:

Worksheet size - The slow model ends at about cell AB1500; the fast model ends at AB2500
No links in either
Moderate graphics - pretty similar in the two files
I never format beyond the active range
Similar number of range names
Same math in each sheet, though the slower one does have some VLookups. (I erased them, but no appreciable change in speed)
VBA turns off autocalc and screenupdating when running

I ran Task Manager when running both macros, and the smaller model showed 171 MB of memory in use; the larger model showed only 140 MB.

I ran the VBA code from the fast model on the slow model and it was still slow -- so truly, it's not the VBA

Any ideas for what it could be?

 

[XLfriend777]

Hi SusaninHouston - Have you tried copying the entire worksheet from each into a new workbook? I have had situations that cleared themselves up when I just copied the data into a new workbook using Ctrl-A, then Ctrl-C and then paste into new workbook. The other thing I have tried on "slow" workbooks is to just take some of the data and put it into a new workbook. Sometimes it is a data issue and you can determine which data is slowing down the macros. Hope this helps get things started. Good luck!

 

[tonyyy]

I never format beyond the active range

Sometimes a loop will cause Conditional Formatting to recalculate on every iteration, thus eating up memory and slowing progressively further into the loop.

Cheers,

tonyyy