Calculate a range always and calculate another range manually, on-demand (same sheet)

[yflpov]

Hi all,

I'm looking for a solution for making the range D3:D53 calculate always and for making the range G4:G53 calculate only on-demand (and apply AutoFit Row Height) via a button. This is an enhancement from having the entire G column calculate on-demand and keeping the calculation for the sheet disabled otherwise.

My current setup is to apply:

ThisWorkbook.Sheets("Timeline").EnableCalculation = False

at Activate and Deactivate the sheet (to make sure no calculation is happening unless I really want it) and apply:

ThisWorkbook.Sheets("Timeline").EnableCalculation = False
ThisWorkbook.Sheets("Timeline").EnableCalculation = True
Columns("G:G").Select
Selection.Rows.AutoFit
Range("B1").Activate
ThisWorkbook.Sheets("Timeline").EnableCalculation = False

via a button when I want to have the formulas in column G recalculated.

The code I've tried is:

Application.Calculation = xlManual
Range("D3:D53").Calculate

at Activate and Deactivate the sheet and apply:

Application.Calculation = xlManual
Columns("G:G").Select
Selection.Rows.AutoFit
Range("B1").Activate
Range("G4:G53").Calculate
Range("D3:D53").Calculate

via a button when I want to have the formulas in G4:G53 recalculated.

This solution gets me what I want from the sheet but the entire workbook remains in Manual calculation mode which I don't want.

I tried adding

Application.Calculation = xlAutomatic

at the end of the Active/Deactivate code and at my button code but it started working against having G4:G53 calculate only on-demand.

I realise the code is not optimised and there are redundancies. Honestly, I'm not terribly experienced with VBA and would rather have those dependencies there for the sake of ensuring proper function. Feel free to add/remove/edit as you see fit.

Thanks to all who try and help!

Best!

 

[yflpov]

Bump. Any thoughts, comments?