VBA Clear Contents

ClearContents is a method in VBA used to delete or remove the values stored in the cells provided to it. This method makes the cell range empty. It is used with the range property to access the specified cell range. An example of this method is range(“A1:B2”). The ClearContents method will clear the contents of cells from A1 to B2.

In Excel, adding and deleting data is a common routine task. 

Sometimes we delete a single cell value, sometimes many cell values, and sometimes we may require to delete the entire worksheet content. This article will show you how to use the “ClearContents” method in Excel VBA. In VBA, we have many methods to do this, like “Clear,” “Delete,” and “Clear Contents.”

Before we tell you about ClearContents in VBA, let me show how we can delete or clear off the data in the specific range.

 Look at the below data.

Now, if we want to clear off cells A1 to C3, we need first to mention the range of cells using the VBA RANGE object.

Code:

Range (“A1:C3”)

After mentioning the range of cells by using the RANGE object, we need to select the method “Clear” to clear off the mention of the cell values.

Code:

Range (“A1:C3”).Clear

It will clear off the mentioned cell values.

Code:

Sub Clear_Example()  Range("A1:C3").Clear End Sub

Apart from the clear method, we can also use the “DELETE” method.

Code:

Range (“A1:C3”).Delete

It will delete the mentioned cell values, just like our clear method.

You can use the VBA CELLS property with a worksheet name if you want to delete all the cell's data.

Worksheets(“Sheet1”).Cells.DeleteWorksheets(“Sheet1”).Cells.Clear

Both the above codes will delete the entire worksheet “Sheet1” data. In addition, it will delete the cell values from the first cell to the last cell of the worksheet.

If you want to delete the present sheet cells, you can use the Active Sheet object.

ActiveSheet.Cells.Delete or ActiveSheet.Cells.Clear

We know this question should have already played in your mind.

However, there is a difference between these two methods.

When you use the method “Delete,” it will delete the cell, and the below cell will take over the position of the deleted cell.

For example, look at the below image.

Now, we will use the delete method to delete cell A1.

Code:

Sub Clear_Example() Range("A1").Delete End Sub

We will run this code and see what happens.

Look what happened here. As we said, when we deleted cell A1, it got deleted, but cell A2 moved one cell up and occupies the deleted cell. So, it will lead to a data mismatch. So, be careful while using the "Delete" method.

Now, for the same data, we will clear the method.

Code:

Sub Clear_Example() Range("A1").Clear End Sub

Now, see what happens when we run this code.

This code has just vacated cell A1 without altering other cells. Therefore, this looks like a proper method to delete only the part of the cells of the entire data range.

If you have observed the previous two methods, those two methods not only deleted or cleared off the cells provided. It also deleted the formatting of the cells we have provided.

To retain the formatting of the cells, we need not use neither “Delete” nor “Clear,” but we need to use the VBA “ClearContents” method.

When you enter the range of cells using a RANGE object, it will show all its properties and methods.

We can access “Delete,” we can access “Clear,” and we can also access “ClearContents” methods.

Select this method.

Code:

Sub Clear_Example()

  Range("A1:C3").ClearContents

End Sub

It will clear content from A1 to C3 cell, but we will have all the existing formatting.

As you can see in the above picture, we have cell color in VBA, borders, and every formatting associated with those mentioned cells.

Similarly, we can clear the contents of other sheets as well.

Worksheets(“Sheet1”).Range(“A1:D10”).ClearContents

It will clear the contents from cells A1 to D10 in sheet “Sheet1”.

Similarly, we can delete the other open workbook cells as well.

Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1:D10”).ClearContents

Assume you have many sheets in your workbook. You want to delete the range of cells from A1 to C15. To do this, we must use For Each Loop in VBA in all the sheets.

The below code will do the job.

Code:

Sub Clear_All() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets Ws.Range("A1:C15").ClearContents Next Ws End Sub

Note: You can change the range of cells as per your wish.

If you want to clear off the entire worksheet data, then you need to use the code below.

Code:

Sub Clear_All() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets   Ws.Cells.ClearContents Next Ws End Sub

You can download this VBA Clear Contents Excel template here - VBA Clear Contents Template.