Deleting Everything Except Formulas

At the beginning of each year Richard makes a copy of the previous year’s Excel workbook. To get ready for the new year’s data, he then needs to delete everything in the copy except for formulas. Richard wonders if there is a quick way to do this.

Actually, there is a very quick way to manually delete all the non-formula information in a worksheet. Follow these steps:

1. Press F5. Excel displays the Go To dialog box.
2. Click Special. Excel displays the Go To Special dialog box. (See Figure 1.)



Figure 1. The Go To Special dialog box.

3. Make sure the Constants radio button is selected. (This is extremely important-don’t click any other option in the dialog box.)
4. Click OK. All the cells containing constants (everything except formulas) are selected.
5. Press the Del key.

If you have a lot of worksheets in a workbook and you want to delete all the constants from each of the worksheets, you might want to use a macro that automates the above steps for the entire workbook.

Sub DelAllConstants()
    Dim wks As Worksheet
    Dim rng As Range

    For Each wks In ThisWorkbook.Worksheets
        On Error Resume Next
        Set rng = wks.Cells.SpecialCells(xlCellTypeConstants, 23)
        On Error GoTo 0
        If Not rng Is Nothing Then
            rng.ClearContents
        End If
        Set rng = Nothing
    Next
    Set wks = Nothing
End Sub