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
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.