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

Posted on 2014-08-27
Last Modified: 2014-08-28
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
Question by:Seamus2626
    LVL 15

    Expert Comment

    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.
    LVL 47

    Accepted Solution


    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
                Set rng3 = Union(rng3, c)
            End If
        End If
    Next c
    Set SubstractRange = rng3
    End Function

    Open in new window

    LVL 26

    Assisted Solution

    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
            Application.Calculation = xlCalculationAutomatic
        End If
    End Sub

    Open in new window

    LVL 47

    Expert Comment


    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
    LVL 26

    Expert Comment

    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.

    Author Closing Comment

    No linkage in my example, all good!


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Printer Setup Pop-up when Opening Excel File 4 15
    Min Month 11 30
    Search Box 13 29
    VBA excel copy code causing excel to crash 3 15
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now