How to Disable Execution of All Formulas Within A Specified Range

[Cyberdude]

I have a worksheet that is functionally divided into ten functionally separated groups of columns. Each group has a lot of formulas that are always executing. There are time periods (days, even months) during which some of these groups are essentially unused, but the formulas within their boundaries are recalculated along with the formulas that I?m actually using in the active groups. In an effort to reduce the computational overhead these ?unused? groups represent, is there is a way I can tell Excel to ignore ? don?t execute ? any of the formulas is the column range of, say, ?G? through ?AK??

I might add that no formulas external to a group are dependent on formulas within a group. However, some of the formulas within a group ARE dependent on cell values external to the group.

I can't delete a group of formulas, because at any time I might find it necessary to "activate" a group and start using it. This can happen on very short notice.

[malik641]

Well, I know you can tell which cells to recalculate (Range(x).Calculate), but I'm wondering if there is a way to say something like

Sheet("Sheet1").Calculate Except/Minus/Less Sheet("Sheet1").Range("G:AK")

or something....:think:


If there is no such thing, my suggestion is to set calculation to manual and either
A: make another sheet and write the range you want to calculate in one cell, i.e. "A:F, AL:IV" and just refer to that cell for the range to calculate

or

B: put the range in a constant in the code and edit the code once in a while

HTH :)

[Cyberdude]

Thanks for the response, Joseph. I was hoping (but not expecting) that someone knew of a seldom-used statement to do the job. I don't want anything complicated. This isn't urgent. I feel like all those formulas just churning away is like leaving all your water faucets running all the time. I often have several of these workbooks open at the same time, so it mounts up. Thanks again.

[malik641]

no problem :thumb

I've done like 3 project that used only manual calculation, and I told excel which cells to calculate (using functions and the Worksheet_Change event). I never had to touch it again, and it sped things up tremendously. This might be a consideration for you.

[Cyberdude]

Hmmmm, interesting idea. I'll give it a try. Thanks, guy! :friends:

[malik641]

No problem, Sid :friends:

[johnske]

This sounds like something that VBA was designed for. Make those columns a VBA procedure or maybe something like this...
Option Explicit

Private Sub Worksheet_Activate()
With Me.UsedRange.Columns("G:AK")
.Value = .Value
End With
End Sub

Private Sub CommandButton1_Click()
Call CalculateFormulas
End Sub

'OR - do calculations on deactivate
Private Sub Worksheet_Deactivate()
Call CalculateFormulas
End Sub

'insert your formulas where needed
Private Sub CalculateFormulas()
With Me.UsedRange
.Columns("G:H").Formula = "=$A1"
.Columns("I").Formula = "=$B1"
.Columns("J").Formula = "=$C1"
'etc
'etc
'etc
End With
End Sub