Clear Cell Content and Formatting using VBA

In this article, we will learn how to clear a cell’s contents, the range of cells or, the named range. We would also learn to remove the formatting of a range or just the contents of a range with the help of VBA. This applies to Active Worksheet or any specific worksheet of the Worksheets Collection.

So let us start learning.

ClearContents Method in VBA

ClearContents is a method in VBA that is used to clear or remove the values stored in our worksheet at a specific range or cell. This method empties the range. First, we use the Range property to access a range followed by the dot operator and then use the ClearContents method.

expression.ClearContents

Here, the expression can be a range of cells, a single cell, or even a named range in the Active Worksheet or any specific worksheet of the Worksheets Collection. We can access a range using the Range property. Similarly, we can refer to a particular cell of the worksheet with the help of the Cells property.

Examples to Implement

In this part of the blog, we would implement some examples to prove what we just learned.

Example 1 – Clear Contents Method – Cell and Range

Let us say we have a worksheet Sheet1 having the following content.

Let us say I wish to remove the contents of cell A1. Since cell A1 is made by the intersection of the first column and the first row of the active worksheet Sheet1, we use the Cells method as follows.

Sub exClearCellContent()
'delete the value in cell A1
Cells(1, 1).ClearContents
End Sub

When you run the Sub Procedure, you will see that it clears what is written in cell A1 but keeps the formatting of the cell.

We would use the Range Property to refer to a range in the active worksheet.

Use the following Sub Procedure to clear the contents of range A2:A12.

Sub exClearRangeContent()
'clear the contents of a range of cells A2:A12
Range("A2:A12").ClearContents
End Sub

We can clear the contents of a named range or a dynamic named range in a simple way shown below.

Range("<nameOfRAnge>").ClearContents

Note that all these Sub Procedures apply on the Sheet which is currently opened at the time when you run the Sub Procedure.

This is how we clear the cell content using VBA.

Example 2 – Clear the Cell Content as Well as Formatting

We can use the Clear method of a Range or Cells property to clear the cell values as well as the formatting applied to the cells. For instance, let us say we have Sheet2 opened containing the data as follows.

Sub exClearContentFormatting()
'clear the cell content and formatting using clear method
Range("A1:D9").Clear
End Sub

When you run this Sub procedure, it clears the contents as well as the formatting of the range A2:D9.

Now we have learned to clear cell contents and formatting using VBA.

Example 3 – Clear Cell Formatting using VBA

In this example, we would learn how to clear the formatting of the cells while keeping the cell content.

Let us say I have the following content in Sheet3 of my workbook.

We want to delete the formatting of cells A1, B3, and Cell C2. We can use the Cells Property to refer to these cells followed by ClearFormatting method to remove the formatting of the referred cell.

Sub exClearCellFormatting()
'clear the cell formatting
'deleting formatting of cell A1
Cells(1, 1).ClearFormats
'deleting formatting of cell B3
Cells(3, 2).ClearFormats
'deleting formatting of cell C2
Cells(2, 3).ClearFormats
End Sub

Run this Sub Procedure to clear the formatting.

Sometimes, we wish to apply the operation on the sheet which is not currently active while we run the code. This is when we can refer to a specific worksheet of the workbook with Worksheets Collection.

Worksheets(Index).Range(<range>).ClearContents

You can learn to refer to a worksheet using the Worksheets Collection from here.

This brings us to end.

Thank you for reading.