VBA Clear Contents

Excel VBA Clear Contents

In excel adding the data and deleting the data in common routine task. Sometimes we delete single cell value, sometimes many cells values and sometime we may require to delete the entire worksheet content as well. In this article, we will show you how to use the “Clear Contents” method in Excel VBA. In VBA we have many methods to do this like “Clear”, “Delete”, and “Clear Contents”.

What is Clear Contents in Excel VBA?

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

For an example look at the below data.

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

Code:

Range (“A1:C3”)

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

Code:

Range (“A1:C3”).Clear

This 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 “DELETE” method as well.

Code:

Range (“A1:C3”).Delete

This will delete the mentioned cell values just like our clear method has done.

If you want to delete all the cells data then you can use VBA CELLS property with worksheet name.

Worksheets(“Sheet1”).Cells.Delete

Worksheets(“Sheet1”).Cells.Clear

Both the above codes will delete the entire data of the worksheet “Sheet1”. It will delete the cell values right from the first cell to the last cell of the worksheet.

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

ActiveSheet.Cells.Delete or ActiveSheet.Cells.Clear

Difference Between Clear & Delete Methods

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

Yes, 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 an example look at the below image.

Now I will use the delete method to delete the cell A1.

Code:

Sub Clear_Example()

 Range("A1").Delete

End Sub

I will run this code and see what happens.

Look what happened here as I told when I deleted the cell A1 it is deleted but the cell A2 moves one cell up and occupied the deleted cell. This will lead to data mismatch. So be careful while using the Delete method.

Now for the same data, I will clear method.

Code:

Sub Clear_Example()

 Range("A1").Clear

End Sub

Now see what happens when I run this code.

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

Use VBA Clear Contents Method to Retain Formatting of Cells

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.

In order to retain the formatting of the cells we need not use neither “Delete” nor “Clear” but we need to use the VBA “Clear Contents” method.

The moment you enter the range of cells by using RANGE object it will show all the properties and method associated with it.

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

Select this method.

Code:

Sub Clear_Example()

  Range("A1:C3").ClearContents

End Sub

Now, this will clear content from A1 to C3 cell but we will have all the existing formatting as it is.

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

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

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

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

Loop Through all the Worksheets and Clear Contents of Specific Range

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

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

Just in case if you want to clear off the entire worksheet data then you need to use below code.

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

Recommended Articles

This has been a guide to VBA Clear Contents. Here we learn how to use Clear, Delete and ClearContents method in VBA to clear data in Excel along with some simple to advanced examples. Below are some useful excel articles related to VBA –

• New Line in VBA MsgBox
• Split String in VBA 
• VBA Borders
• Use Count in VBA
• VBA AutoFill | Examples
• How to use Do Until Loop in VBA?
• How to Use Timer in VBA?
• Use Progress Bar in VBA
• VBA Value Examples