Macros running slow on Excel 2003 but not on Excel 2002

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.

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
[email protected]
http://www.vonixx.com



"James" wrote:

> 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.

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????

"Edwin Tam" wrote:

> 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
> [email protected]
> http://www.vonixx.com
>
>
>
> "James" wrote:
>
> > 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.

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

"James" <[email protected]> wrote in message
news:[email protected]...
> 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????
>
> "Edwin Tam" wrote:
>
>> 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
>> [email protected]
>> http://www.vonixx.com
>>
>>
>>
>> "James" wrote:
>>
>> > 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.

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

Originally Posted by 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> Why did they do that? What is the rational behind it?
>
>
>
> Charles Williams Wrote:
>> 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
> ------------------------------------------------------------------------
> Infinity's Profile:
> http://www.excelforum.com/member.php...o&userid=32725
> View this thread: http://www.excelforum.com/showthread...hreadid=534058
>

Thanks Charles Williams.

That clears all the question mark that i have.