Microsoft Excel Functions & Formulas

Use a custom function to change all cells with

formulas to values

This macro changes all cells with formulas to cells containing val-

ues. Note that all formulas will be deleted. This is a common need

when copying tables where we need just the results of a calculation

and no formulas or individual formattings.

4

To change all formulas into values:

1. Press <Alt+F11>.

2. On the Insert menu, click Module.

3. Type the following macro:

Sub ChangeToValue()

Dim rng As Range

With ActiveSheet

For Each rng In .UsedRange

rng.Value = rng.Value

Next rng

End With

End Sub

Note: To start the macro from the Visual Basic Editor, click

anywhere within the macro code and press <F5>. ...