CODEWITHSUNDEEP
vbaexcelexcel-formulacalculated-columns
Danny
Danny

Reputation: 574

turn on/off automatic calculation in only a few columns in Excel

In excel I have a worksheet with over 30,000 rows. Sample data is shown in the image below. About a dozen of the columns have formulas which really slow down the work whenever I update a cell. I would like to use VBA code to turn off automatic formula calculation for only 5 columns (see columns in red in example). The formulas in the columns in yellow would run all the time. I would then like to create a macro that calculates the formula in the red columns whenever pressed.

I tried looking for some options in the formula ribbon but wasn't successful.

enter image description here

Upvotes: 8

Views: 38762

Answers (2)

j.dupaon
j.dupaon

Reputation: 11

Why don't you simply write a macro that implements the formulas and then replaces the formulas with their results at the end. The cells would only be "dynamic" when the macro is running.

Upvotes: 1

Ricardo González
Ricardo González

Reputation: 1433

If you are creating a macro

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculateManual

[YOUR CODE HERE]

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

But if you just want to enable and disable.. Go to the menu (from the ribbon) Formulas / Calculation Options and select Automatic or Manual as desired.

Upvotes: 10

Related Questions