to turn Calculation OFF for all except one column

alienscript · December 17th, 2003, 04:34 AM

Hi the experts,

I have a data worksheet ranging from column A to CR and covering 5200 rows. I have maintained a few columns in between with Sumproduct and If(Countif()) formulas.

Whenever I insert a new column, say column K and vlookup for values in other worksheets, the calculation takes very long and at times just hang.
How do I turn Calculation ON for only the active column (column K) and turn Calculation manually OFF for the rest of the columns ?

I would be most grateful if someone can help me with a code to solve this.

Thank you so much in advance!

chrislepingwell · December 17th, 2003, 09:01 AM

If your are just concerned about the amount of time it takes to insert the column, then you can set Calculation to Manual (Tools|Options|Calculation). Then insert your column, and either set Calculation back to Automatic, or Press F9 (recalc) or shift-F9 (recalc current worksheet only). Obviously, you don't have to do this instantly, particularly if you have more alterations to make, just remember to do it before you use any numbers :)
If you are trying to do this through code, then use

Code:

Application.Calculation = xlManual
...stuff...
Application.Calculation = xlAutomatic

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know