Deleting Numbers

Sometimes it is useful to delete all the input data in a worksheet or template so it is more obvious where new values are required. The following code deletes all the numbers in a worksheet, leaving the formulas intact:

On Error Resume Next

Cells.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

The preceding code should begin with the On Error statement if you want to prevent a run-time error when there are no numbers to be found.

Excel considers dates as numbers, and they will be cleared by the preceding code. If you have used dates as headings and want to avoid this, you can use the following code:

On Error Resume Next

For Each rng In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

If Not IsDate(rng.Value) Then rng.ClearContents Next rng

0 0

Post a comment