EDUCBA

Home VBA VBA Resources VBA Coding VBA Clear Contents
 

VBA Clear Contents

Madhuri Thakur
Article byMadhuri Thakur

Updated June 12, 2023

VBA Clear Contents

 

 

Introduction to Excel VBA Clear Contents

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 must keep in mind. With clear contents, we only clear the data present in the cell. Clear contents do not do anything to the cells’ formatting or conditional formatting. A different function does that.

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 the clear contents method to clear the data present in the excel sheet. The syntax to use the 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 to clarify it. For the demonstration, I have data in different worksheets.

Note: In order to use VBA in Excel, please ensure that we have the developer’s tab enabled from the files tab in the options section.

Examples of Excel VBA Clear Contents

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

You can download this VBA Clear Contents Excel Template here – VBA Clear Contents Excel Template

Excel VBA Clear Contents – Example #1

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

VBA Clear Contents Example 1

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

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

VBA Clear Contents Example 1-1

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

VBA Clear Contents Example 1-2

Step 3: Start VBA Code by sub-function.

Code:

Sub Sample()

End Sub

VBA Clear Contents Example 1-3

Step 4: To use any worksheet properties, we must first activate the worksheet. Activate the worksheet with the following code.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

VBA Clear Contents Example 1-4

Step 5: Use the 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

VBA Clear Contents Example 1-5

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

Result of Example 1-6

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 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.

VBA Clear Contents Example 2-1

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

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

Code:

Sub Sample1()

End Sub

\VBA Clear Contents Example 2-2

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

VBA Clear Contents Example 2-3

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

Code:

Sub Sample1()

Worksheets("Sheet2").Activate

Range("A1:C3").ClearContents

End Sub

VBA Clear Contents Example 2-4

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

Result of Example 2-5

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 purposes, I have data in sheet 3 in Light Blue color. Have a look at it below,

Example 3-1

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

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

Code:

Sub Sample2()

End Sub

VBA Clear Contents Example 3-2

Step 2: We know that 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

VBA Clear Contents Example 3-3

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

Code:

Sub Sample2()

Worksheets("Sheet3").Activate

Range("A1:C3").ClearContents

End Sub

VBA Clear Contents Example 3-4

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

Result of Example 3-5

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, which is in bold and italic format. Once we have cleared the contents, we will return some data in those cells to see if the formatting is present. Look at the data below; Cell A1 is in Bold Format, while Cell B1 is in Italic format.

Example 4-1

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

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

Code:

Sub Sample3()

End Sub

VBA Clear Contents Example 4-2

Step 2: We know that 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

VBA Clear Contents Example 4-3

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

Code:

Sub Sample3()

Worksheets("Sheet4").Activate

Range("A1:B1").ClearContents

End Sub

VBA Clear Contents Example 4-4

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

Example 4-5

Now try to add some random values in cells A1 and B1 to check if the formatting is still intact.

Result of Example 4-6

We can see that we only cleared the contents while the formatting remained.

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 affect the formatting of the cells.
  • Even if the data is in conditional formatting, clear contents do not clarify the cells’ formatting.

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 a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA Month
  2. VBA IsError
  3. Excel VBA MsgBox
  4. VBA Create Object