Speed me up please with VBA

[cdjsb]

I have a very complex spreadsheet which I have had to turn the automatic calculation off as at present the 15 sheets take around 25 minutes to calculate. Could anyone please tell me the code I need to write a bit of VBA (which would be attached to a macro button) to make it so on each page I could just calculate a range of cells on that particular worksheet. So then I can go from this to this

[davidm]

Use the following set of Events (assuming you want to freeze calculations in all sheets safe for Range("a1:e10") in some sheets:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
End Sub

Deploy the following 2 events in the selected sheets.

Private Sub Worksheet_Activate()
Range("a1:e10").Calculate
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("a1:e10").Calculate
End Sub

[cdjsb]

Thanks for that David:

One more quick question How about if I highlight an area of cells on the spreadsheet and then want just them to be recalculated, Do you know of a way of making excel do just that.

[cdjsb]

One quick question if I highlight an area of cells on the spreadsheet and then want just them to be recalculated, Do you know of a way of making excel do just that.
Can anyone please tell me the vba code I would need to use.
Thanks

[davidm]

use:

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


Private Sub Worksheet_selectionChange(ByVal Target As Excel.Range)
Range("a1:d10").Calculate
End Sub

[anilsolipuram]

Dim r As Range
Set r = Selection
r.Calculate