8

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Danny
  • 554
  • 1
  • 6
  • 17
  • Another conceivable solution: move some columns to another tab, and find VBA or some other way to control which tabs recalculate. Or possibly in another workbook: https://excel.tips.net/T001988_Forcing_Manual_Calculation_For_a_Workbook.html – Ray Woodcock Jul 16 '22 at 17:13

2 Answers2

9

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.

Ricardo González
  • 1,385
  • 10
  • 19
  • Thanks 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. – Danny Apr 10 '18 at 01:29
  • 1
    I 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. – Ricardo González Apr 10 '18 at 01:58
  • @ricardo Well a way to turn off calculations for certain cells would be to rewrite the formulas as custom UDFs then don't include `application.volatile` – Cameron Critchlow Jan 09 '23 at 23:12
1

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.

j.dupaon
  • 11
  • 2