Changing formulas to values is something really easy in MS Excel – what the majority of people (including me) would do the following – simply record a macro, where you select all cells and paste them as values. The result would look like this:
|
1 2 3 4 5 6 |
Sub Macro1() Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False End Sub |
Actually, it is not that bad, as far as it functions. Anyway, there is a better and probably faster way to do the same operation:
|
1 2 3 4 5 6 7 8 |
sub CopyRangesWithFormulas cells.Select For Each CurrentCell In Selection If CurrentCell.HasFormula = False Then CurrentCell.formula = CurrentCell.Value End If Next end sub |
With this macro we do not use copy and paste, but a better method simply assigning the value to the cell. I prefer it, because it is a little faster and when the file size is bigger than a few MB, then the speed is obvious.
Enjoy!