Avoid recomputation in a large excel worksheet

  • Thread starter Thread starter cox
  • Start date Start date
C

cox

I have a worksheet with 2000 rows and 20 columns. All of these cells have
formula to compute their values. Each formula gets recomputed even when the
data used the computation have not changed - in short it takes an enormous
time to recompute 40000 data whose values have not changed. Is there anyway
to make excel stop recomputation when it is known that the values will not
change, because none of those cells whose values are used in computation has
been updated?
 
This macro will convert the formulas to values for whatever selection you
make. It could be designed to do the selection for you
Sub converttovalues()
With Selection
..Value = .Value
End With
End Sub

It could be designed to do without selections but you may want to retain
some formulas

Sub converttovalues2()
With ActiveSheet.UsedRange
..Value = .Value
End With
End Sub
 
Maybe it's your formula that needs to be reviewed?

You may want to post it to see if anyone can help.
 
Back
Top