turn on/off automatic calculation in only a few columns in Excel
19,198
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.
Author by
Danny
Updated on July 02, 2022Comments
-
Danny almost 2 years
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.
-
Danny about 6 yearsThanks for the suggestion but I'm trying to disable automatic calculation for 5 out of the 12 columns that have formulas. The macro would then automatically calculate the 5 columns that are disabled whenever pressed. I've modified my question to include a screenshot with some sample data.
-
Ricardo González about 6 yearsI am pretty sure there is an alternate way of doing whatever you intend to do. As far as I know, there is no way to stop-calc specific range of cells/rows/columns.