VBA Clear Contents

Introduction to Excel VBA Clear Contents

While working in excel we come around in a point when we have to remove the data already present in a cell or in range of cells for another function or any other command to execute. This is done manually if we are working in a worksheet. But if we are working in VBA we use clear contents method to clear the data or values present in the cells.

Clear contents is a range function in excel which is used to clear the contents in a given range of cells or group of cells. Clearing data and clearing cells are different things we need to keep in mind. With clear contents, we only clear the data present in the cell. Clear contents do not do anything to the formatting or the conditional formatting of the cells. That is done by a different function.

To clear contents, we need to select the range of cells we want to clear and once we have identified the cells we can use clear contents method to clear the data present in the excel sheet. The syntax to use clear contents method in VBA is as follows:

Range(“Cell Range“).ClearContents

In the cell range, we provide the cell range which we want to clear.

Let us use this function in a few examples which will make it more clear for us. For the demonstration, I have data in different worksheets.

Examples of Excel VBA Clear Contents

Below are a few practical examples of the VBA Clear contents in excel.

Excel VBA Clear Contents – Example #1

In sheet 1 I have some data in cell A1. Have a look at it below.

We will use clear contents method to clear the data from cell A1. Follow the below steps to clear the content of the cell using VBA code.

Step 1: Go to developer’s tab and click on Visual Basic to open VB Editor.

Step 2: It will open the VB Editor for us. Click on Insert tab to insert a new module.

Step 3: Start VBA Code by sub-function.

Code:

Sub Sample()

End Sub

Step 4: To use any properties of a worksheet we need to first activate the worksheet. Activate the worksheet with the following code.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now use Clear contents function to clear the data in cell A1 with the following code.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

Range("A1").ClearContents

End Sub

Step 6: Run the above code from the run button provided or press F5.

Once we have run the code we can see the result of cell A1 in sheet 1 that the data is gone.

Excel VBA Clear Contents – Example #2

In the above example, we cleared the contents of a single cell but what we have data in a range of cells. Will this function work? We will find out the same in this example. I have data in sheet 2 as follows.

We will use clear contents function to clear the data in these range of cells. Follow the below steps to clear the content of the cell using VBA code.

Step 1: In the code window, declare sub-function for code writing.

Code:

Sub Sample1()

End Sub

Step 2: To use the properties of sheet 2 always remember to activate the worksheet by the following code.

Code:

Sub Sample1()

Worksheets("Sheet2").Activate

End Sub

Step 3: We know that we have data in the cell range A1:C3 in sheet 2. We will use clear contents function to clear the contents from that cell range.

Code:

Sub Sample1()

Worksheets("Sheet2").Activate

Range("A1:C3").ClearContents

End Sub

Step 4: Run the above code from the run button provided or press F5 to get the following result.

We can see that the data from cell range A1:C3 has been cleared.

Excel VBA Clear Contents – Example #3

We discussed about formatting earlier in the article. Do clear contents also clear the formatting of cells with the contents? We will see that in this example. For demonstration purpose, I have data in sheet 3 in Light Blue color. Have a look at it below,

Follow the below steps to clear the content of the cell using VBA code.

Step 1: Start the code by declaring sub-function.

Code:

Sub Sample2()

End Sub

Step 2: We know that in order to use the properties of sheet 3 we have to activate the worksheet by the following code.

Code:

Sub Sample2()

Worksheets("Sheet3").Activate

End Sub

Step 3: We know that we have data in the cell range A1:C3 in sheet 3. We will use clear contents function to clear the contents from that cell range.

Code:

Sub Sample2()

Worksheets("Sheet3").Activate

Range("A1:C3").ClearContents

End Sub

Step 4: Run the above code from the run button provided or press F5 to get the following result.

We can see that the data from cell range A1:C3 has been cleared but the format of the cells is still intact.

Excel VBA Clear Contents – Example #4

In this example we have some data in sheet 4, the data is with bold and italic format. Once we have cleared the contents we will put back some data in those cells again to see if the formatting is present or not. Have a look at the data below, Cell A1 is in Bold Format while Cell B1 is in Italic format.

Follow the below steps to clear the content of the cell using VBA code.

Step 1: Start the code by declaring sub-function.

Code:

Sub Sample3()

End Sub

Step 2: We know that in order to use the properties of sheet 4 we have to activate the worksheet by the following code.

Code:

Sub Sample3()

Worksheets("Sheet4").Activate

End Sub

Step 3: We know that we have data in the cell range A1:B1 in sheet 4. We will use clear contents function to clear the contents from that cell range.

Code:

Sub Sample3()

Worksheets("Sheet4").Activate

Range("A1:B1").ClearContents

End Sub

Step 4: Run the above code from the run button provided or press F5 to get the following result.

Now try to put some random values again in cell A1 and B1 to check if the formatting is still intact.

We can see that we only cleared the contents while the formatting is still there.

Things to Remember

• VBA Clear Contents can clear data from a cell or given cell range.
• Clear contents only clear the data from the cells it does not affects the formatting of the cells.
• Even if the data is in conditional formatting, clear contents do not clear the formatting of the cells.

Recommended Articles

This is a guide to VBA Clear Contents. Here we discuss the examples to clear the content of the cell using Excel VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Examples to Use VBA Enum
2. Grouping Columns in Excel
3. VBA Delete Column Example
4. Concatenation in Excel