How to speedup a macro.....

Posted by newby on August 06, 2001 9:15 AM

Hello again guys,

Don't know if this possible..I run 1 of my macro
and it take nearly 3 minutes to finish..How can
I speed it and make it run a little(more faster is better)..
I know "application.screenupdating" will not works in this
method coz i have try it..it will only hide what's going
on but not increasing the code peed..any idea..
my range that i run using the macro is from b9:ab78
its not big though..but its took me nearly 3 minutes to
run the macro..any help will be appreaciated

ps: if you know the method..please guide me where should i put
it..i.e maybe after the variable , or before the end sub or etc..
thanks again

Posted by Joe Chin on August 06, 2001 12:21 PM

Put this at the beginning of the macro:

Application.Calculation = xlCalculationManual

And put this at the end of the macro:

Application.Calculation = xlCalculationAutomatic

You might also want to save the current user's calculation settings before and after the code, in a separate variable.