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
  • 18,752
  • 7
  • 68
  • 96
BuZz
  • 14,398
  • 28
  • 80
  • 133

3 Answers3

31

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
aevanko
  • 14,283
  • 4
  • 50
  • 57
  • 1
    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,176
  • 1
  • 21
  • 28
-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
  • 2,877
  • 15
  • 33
  • 38
Pituś
  • 1