Excel Macro: Clearing Data Not Formulas

This Excel VBA macro clears only the data (not formulas) from an active worksheet.

Macro Example

Sub ClearDataNotFormulas()

  On Error Resume Next

  Cells.SpecialCells(xlCellTypeConstants).ClearContents

End Sub

Description

On Error Resume Next

Sub...

  On Error Resume Next

A run-time error will occur at the 5th line, if there is not any cells that have data.

The 3rd line aims to ignore this run-time error.

Clearing Values

Sub...

  On Error Resume Next

  Cells.SpecialCells(xlCellTypeConstants).ClearContents

The 5th line clears only the data from an active worksheet.

(Application.)Cells

Cells...

The Application.Cells property returns the Range object represents ALL ranges on an active sheet.
So we do not need to loop through.

obj-Excel-Application-Cells

The Cells property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Cells

Cells.SpecialCells (Range.SpecialCells)

Cells.SpecialCells...

The Cells.SpecialCells (Range.SpecialCells method) returns a Range object that represents all the cells that match the specified type and value.

obj-Excel-Range-SpecialCells

Cells.SpecialCells(xlCellTypeConstants)...

The Range.SpecialCells method requires the Type parameter that represents the type of special cells. In this case, I use xlCellTypeConstants.

obj-Excel-XlCellType-xlCellTypeConstants

It refers to cells that contain any data (not formulas).

SpecialCells(xlCellTypeConstants).ClearContents (Range.ClearContents)

Cells.SpecialCells(xlCellTypeConstants).ClearContents

The Range.ClearContents method clears the data.

obj-Excel-Range-ClearContents

Ends the Macro

After the 5th line evaluated, this macro ends.

Sub ClearDataNotFormulas()

  On Error Resume Next

  Cells.SpecialCells(xlCellTypeConstants).ClearContents

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-clear-constants.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007