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:
- Press F5. Excel displays the Go To dialog box.
- Click Special. Excel displays the Go To Special dialog box. (See Figure 1.)
- Make sure the Constants radio button is selected. (This is extremely important-don’t click any other option in the dialog box.)
- Click OK. All the cells containing constants (everything except formulas) are selected.
- Press the Del key.
Figure 1. The Go To Special dialog box.
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