[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

Set a range to not calculate and then on pressing a button, calculate

I have a range of cells (A1:A10) in Sheet "xyz"

They have very demanding formula which i do not want to affect the rest of my spreadsheets performance.

Can (perhaps on opening the spreadsheet) the autocalc on this range be disabled and only gets enabled when a user clicks a button, so i may need a line of code to disable on opening and a line of code to put in a sub, that i can assign a button too.

Many thanks
0
Seamus2626
Asked:
Seamus2626
2 Solutions
 
ChloesDadCommented:
I don't believe that you can turn off autocalc on a range of cells, but you could put the formulae of those cells into a macro and then run the macro when a key combination is pressed.
0
 
Rgonzo1971Commented:
Hi,

You can have no calculation on a sheet and calculate every time the sheet changes only the used range minus the range to be spared
to calculate everything press F9
Place the code in the sheet module

Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub
Private Sub Worksheet_Deactivate()
Application.Calculation = xlAutomatic
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
SubstractRange(UsedRange, Range("A1:A10")).Calculate
End Sub


Function SubstractRange(rng1, rng2) As Range

Set rng3 = Nothing

For Each c In rng1
    If Intersect(c, rng2) Is Nothing Then
        If rng3 Is Nothing Then
            Set rng3 = c
        Else
            Set rng3 = Union(rng3, c)
        End If
    End If
Next c

Set SubstractRange = rng3

End Function

Open in new window

Regards
0
 
MacroShadowCommented:
You could turn off autocala when selection is in a specific range. Try this code, put it in the xyz worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Application.Calculation = xlCalculationManual
    Else
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
@MacroShadow

with your code the problem is that if the cells inside A1:A10 are linked to ones outside A1:A10 then it will recalculate them as well
0
 
MacroShadowCommented:
Good point. Let's see what the OP has to say, for all you know they may not be linked to cells outside the range.
0
 
Seamus2626Author Commented:
No linkage in my example, all good!

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now