Calculate [RESOLVED]

[Fonty]

I would like to know if it is possible to use Calculate in a workbook, only for a specific range? Instead of calculating the whole workbook.

[Siddharth Rout]

use Calculate in a workbook, only for a specific range? Instead of calculating the whole workbook.

Unfortunately, there is no direct method to just calculate a specific range instead of calculating the whole workbook.

You can, however, set the calculation mode to manual by following these steps:

Choose Options from the Tools menu. Excel displays the Options dialog box.
Make sure the Calculation tab is displayed.
Select the Manual option.
Click OK.

Now the only time your workbook (actually, all your open workbooks) will be recalculated is when you press F9. If you want to recalculate only the current worksheet, then press Shift+F9.

If you want you can now write a macro function that allow you calculate a specified range of cells on a worksheet. With this knowledge you could create a macro that would loop through all the worksheets in a workbook and recalculate each of them.

The following macro sets the calculation mode to manual

Sub CalcBook()
    Dim wks As Worksheet
    Application.Calculation = xlManual
    Set wks = Nothing
End Sub

If you believe that you may want to calculate different parts of your workbook at different times, you can expand the macro so that it will perform any type of calculation you may want:

Sub CalWhat()
    Dim iAnsure As Integer
 
    Application.Calculation = xlManual
    iAnsure = InputBox("1 = Calculate A Used Range" _
      & vbCrLf & _
      "2 = Calculate This Worksheet" _
      & vbCrLf & _
      "3 = Calculate This Workbook" _
      & vbCrLf & _
      "4 = Calculate All Workbooks in Memory" _
      & vbCrLf & vbCrLf & _
      "Input Your Selection Number From Above" _
      & vbCrLf & "Then Click OK", _
      "Calculate What?", "Input Number Please", _
      5000, 5000)
 
    Select Case iAnsure
        Case 1 'Range Only
            Selection.Calculate
        Case 2 'Worksheet Only
            ActiveSheet.Calculate
        Case 3 'Workbook Only
            For Each wks In ActiveWorkbook.Worksheets
                wks.Calculate
            Next
        Case 4 'All Open Workbooks
            Application.CalculateFull
        End
    End Select
End Sub

This macro presents an input box that prompts the user as to which type of recalculation is desired. When the user enters a number from 1 to 4, the desired type of recalculation is performed.

Hope this helps...