Macros running slow on Excel 2003 but not on Excel 2002

[Guest]

I have a macro which hide unwanted rows (2500++ rows).

When i run this macro on Excel 2002 everything is fine and very fast (around
10 sec). BUT when i run it on my colleague PC (which has better spec then my
PC) and on Excel 2003, the macro runs like a SNAIL.

What gives?????????????? It is only running fast when I set the Calculation
from Auto to Manual.

I know it is easy to just add in two more lines in my macros BUT i have
something like 100++ Excel files which has this type of macro and those files
are not small. Their size ranges from 5MB to 30MB. Needless to say there are
a lot of macros in each file.........

Why is Microsoft doing this to me???? *sobs*

Would appreciate if anybody can give me an alternative solution (i dont want
to spend the next few days just changing macros).

Thanks in advance.

 

[Guest]

I don't have a direct answer to this question. But if you have a macro
function which is contained in a lot of Excel files, you may consider making
an Excel add-in file. You can install that add-in file, so that the function
can be made available from the dropdown menu. When the user need to run the
function, the same macro is executed, and when you want to update the add-in,
you just need to change the file once.

An Excel add-in is to have macros detached from the day-to-day Excel files.
This will streamline the whole updating process.

In your case, I think you may consider just give up updating the macros in
individual Excel files, and produce a "general" macro which will work on any
"activeworkbook". And save the macro into an add-in file. If you need help on
making a custom dropdown menu, you may ask for help in this newsgroup. :>

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Guest]

Thanks....... Uh.. but how do you do an Excel Add-in?

And most of the macros in each files are running differently and nearly all
the files are unique/different. e.g. some of them are hiding rows in
different columns and in each file has something like 10-100 worksheets and
they are performing this hiding of rows differently... i mean on different
columns, some just need to lookup 50 rows and hide the unnecessary while
others are looking up to 300 to 3000 rows.

The are also copy-paste-values and then the data is resorted again to get
the information required.

What is Excel 2003 DOING?????? Why cant it be like 2002????

 

[Charles Williams]

Microsoft made a change in Excel 2003 so that hiding a row triggers a
recalculation.
The only way around this is for your macro to set calc to manual, hide the
rows, then reset calc to automatic.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Infinity]

Why did they do that? What is the rational behind it?

 

[Charles Williams]

They added function to the SUBTOTAL() function so that you can exclude
hidden rows from the subtotal.
To make sure that this option in SUBTOTAL always returns the correct answer
they made Excel recalculate whenever a row is hidden, regardless of whether
the SUBTOTAL function is being used.

Unfortunately this also has the effect of significantly slowing down hiding
rows in Automatic Calculation mode.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Infinity]

Thanks Charles Williams.

That clears all the question mark that i have.