17

is it possible to recalculate only a cell or a range in VBA ? What I like to use for a sheet is the following, but how to do it on a cell only ? Or range ?

'    ActiveSheet.EnableCalculation = False
'    ActiveSheet.EnableCalculation = True
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
BuZz
  • 16,318
  • 31
  • 86
  • 141

3 Answers3

32

There is a calculate method you can call on ranges:

Range("A1").Calculate

Try it out by putting =Now() in A1 and running Calculate and watch it update the seconds :) You can for a recalc of all the cells in a sheet by using:

Sheets(1).Calculate

See also: Microsoft MSDN, Excel Recalculation, 16 July 2012.

gerdami
  • 23
  • 6
Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
  • 2
    This recalculates entire workbook, not specified range :-( – Philipp Munin Feb 21 '17 at 19:11
  • 1
    You need to set the calculation method to manual. If it is automatic then Excel will take the opportunity to calculate every workbook that is open (not just the activeworkbook). – Jay Killeen Jul 26 '18 at 01:13
0

Calculate: All open workbooks

Application.Calculate

Calculate: A specific worksheet

Worksheets(1).Calculate

or

Worksheets("sheetname").Calculate

Calculate: A specified row

Worksheets(1).Rows(2).Calculate

or

Worksheets("sheetname").Range("A1").EntireRow.Calculate

Recalculate in Excel

Select the cells you would like to recalculate and press:

Press F9
Unicco
  • 2,466
  • 1
  • 26
  • 30
-1

Try this:

Range("A1").Formula = Range("A1").Formula

or

Cells(1, 1).Formula = Cells(1, 1).Formula

It works for me.

sɐunıɔןɐqɐp
  • 3,332
  • 15
  • 36
  • 40
Pituś
  • 1
  • 1