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

vba excel excel-formula calculated-columns
Danny picture Danny · Apr 10, 2018 · Viewed 14.4k times · Source

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

Answer

Dr Yunke picture Dr Yunke · Apr 10, 2018

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.

Related questions

Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More
What's the quickest way to multiply multiple cells by another number?

If I have a block of cells such as A1:D5, what's the quickest way of multiplying each cell by another number, 1.1 using Microsoft Excel?

Read More
How can I color dots in a xy scatterplot according to column value?

Consider the following worksheet: A B C D 1 COMPANY XVALUE YVALUE GROUP 2 Apple 45 35 red 3 Xerox 45 38 red 4 KMart 63 50 orange 5 Exxon 53 59 green I have used the scatterplot function in Excel to create the following chart: However, each point in the chart has …

Read More