Delete Excel Range Values/Formulas by VBA ClearContents

How to clear contents of Excel cells/range using VBA

The ClearContents is an Excel function that can be used in VBA to remove values and formulas in the range.

However, formatting and conditional formatting remain intact when using the ClearContents function.

An example of removing the value of A2 cell

Following sheet is used for our examples in this tutorial:

We will remove the A2 cell value by using the ClearContents function as follows:

Sub clear_content_ex()

Range("B3").ClearContents

End Sub

Result:

Removing multiple cells' data

We will delete data from A3 to B6 range of cells:

Sub clear_content_ex()

'Clear values of range of cells

Range("A3:B6").ClearContents

End Sub

Result:

Note: If you try to recover deleted value by Ctrl+Z, or Undo from the menu - this will not work.

Clear contents from some other sheet than active

If you want to remove values from another sheet than the active one (or just want to specify the sheet name), you can do this as follows:

Sub clear_content_ex()

'Clear values of another sheet than active

Worksheets("Sheet2").Range("A3:B4").ClearContents

End Sub

How did it work?

• Suppose, our active sheet is “Sheet1”.
• We specified “Sheet2” in the Worksheets object.
• This code will clear contents of the Sheet2’s A3 to B4 cells.

Check if the formula is also removed

In this sheet, we used TRIM() formula in C2 to C6 cells:

You can see, we also applied formatting to the C column cells along with the Trim formula.

After executing the ClearContents function, let us see what result we get:

Sub clear_content_ex()

'Clear values and formulas

Worksheets("Sheet3").Range("C2:C6").ClearContents

End Sub

Result:

You can see:

• Values are gone.
• Formulas are gone.
• The formatting is still remains.