VBA Clear Content

What is Excel VBA Clear Contents?

VBA Clear Contents is deleting or removing the data from a specified range or cell in a given worksheet. It empties a cell or a range by removing all the contents in those cells.

Here, contents mean data, so Clear Contents means nothing but removing the data from the specified cell references. ClearContents is a method in VBA. For example, look at the following data in the Excel worksheet.

Assume we must remove the data from cells A6 and B6. Then, we can use the following code.

Sub Clear_Contents_Intro()
Range(“A6:B6”).ClearContents
End Sub

Once we run this code, it will remove the data from cells A6 and B6.

As we can see in the above image, the data (content) in cells A6 to B6 has been removed and they are empty now. We have cleared the contents of cells in the range A6 to B6.

What are Clear Contents in Excel VBA?

VBA Clear Contents is just removing data from a given cell range. To understand clearly how this works, we must have a practical approach. Following is the data we have in a worksheet.

Assume we must clear contents from cells C1 to D6. We can use three clear methods which are:

• Clear
• Delete
• Clear Contents

First, let’s look at what the Clear method does.

Clear: First, we must provide the cell references of those cells from which we must clear contents. In this case, we must reference cells from C1 to D6 using the Range object.

After providing the cell addresses, enter a dot. You can see the IntelliSense list, which shows all the properties and methods of the Range object.

Choose the “Clear” method from the IntelliSense list.

That’s it; this will remove the contents from cells C1 to D6.

Sub Clear_Contents()
Range(“C1:D6”).Clear
End Sub

Execute the code, and we get the following output.

All the data and formatting have been removed from the range C1 to D6.

Delete: The Delete method works similarly to the Clear method. Let’s have the same data that we had for the previous example.

For the same code from the above example, change the Clear method to Delete.

Sub Clear_Contents_Delete()
Range(“C1:D6”).Delete
End Sub

When we execute this code, we will get the following result.

This result is the same as the previous one.

Clear Content: This is different from the previous two methods. Let’s use the same data from the last two examples.

Change the Delete or Clear method in the above examples to the ClearContents method in the code.

Sub Clear_Contents()
Range(“C1:D6”).ClearContents
End Sub

Execute the code, and we will get the following result.

The result is different in the Clear Contents method. As it has removed only the content, the rest are as it is. The rest means the formatting, like the background color, borders, etc.

These are the three different ClearContents methods available in VBA. Even though both “Clear” and “Delete” methods give the same result, there is a difference between these two, and let’s explore that in the next section of this article.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Difference Between Clear and Delete Methods

Let’s use the following data to understand the difference between the Clear and Delete methods.

Assume we need to remove the month “Apr” content from cell A5 to D5.

Sub Clear_vs_Delete_Clear()
Range(“A5:D5”).Clear
End Sub

Once we execute this code, we will get the following output.

The Clear method has removed all the values from the range A5 to D5. Here, let us observe a couple of things. The formatting in the referenced range has been removed, and the range has become empty. Now change the Clear method to Delete.

Sub Clear_vs_Delete_Clear()
Range(“A5:D5”).Delete
End Sub

Before we run the code, let’s have the old data with all the formatting back.

Once we execute this code, we will get the following output.

The Delete method has removed the data in the range A5 to D5 along with all the formatting, and at the same time, the cells below have moved one row up to acquire the position of the removed data range.

The data range of A6 to D6 has become A5 to D5 once the Delete method is executed.

How to Use VBA Clear Contents Method to Retain Formatting of Cells?

As we have seen, the VBA Clear and Delete method will remove the data and format the given range reference. For example, look at the following data in Excel.

Assume we have ten worksheets where we must eliminate the data in specific ranges across them. We must not write ten different macros or code to clear content.

Once the range is given, we can use the abovementioned methods. They are Clear, Delete, and Clear Formatting. Let’s try the Clear method first.

Sub Clear_Without_Formatting()
Range(“A1:F5”).Clear
End Sub

When we execute the code, we get the following result table.

The data and formatting have been wiped out; hence we see a blank space in the range A1 to F6. Now, use the Delete method.

Sub Clear_Without_Formatting()
Range(“A1:F5”).Delete
End Sub

Execute this code and see the result.

The data in range A1 to F6 has been removed; at the same time, those rows are also removed; hence all the below cells shifted up; hence we don’t see a blank space in range A1 to F6.

Similarly, replace the Delete method with the ClearContents method.

Sub Clear_Without_Formatting()
Range(“A1:F5”).ClearContents
End Sub

Once we execute this code, we get the following result.

The VBA Clear Contents method has removed only the content from the range A1 to F6, and all the formatting is retained. Unlike the Delete method, which has shifted all the cells below upwards, the cells below were not shifted up.

Hence, whenever you must delete something but must retain the formatting, VBA ClearContents is the best. Similarly, we use the following code to clear the content from a different worksheet.

Sub Clear_Content_Worksheet()
‘Define a variable to assign the targeted worksheet reference where we need to clear the content
Dim Ws As Worksheet
‘Set the reference for the worksheet using the set key word
Set Ws = Worksheets(“Basic”)
‘Define a variable to set the targeted range reference
Dim Rng As Range
‘Set the targeted range referene for the variable
Set Rng = Range(“A1:D5”)
‘Apply clear contents method to clear the data from the targeted range
Ws.Range(“A1:D5”).ClearContents
End Sub

The above code will remove the content in the worksheet “Basic” and in the range A1 to D5 from any worksheet.

We can remove the content from any workbook opened in our computer, not just the worksheet of the active workbook. For example, look at the following code.

Sub Clear_Content_Workbook()
‘Define a variable to assign the targeted workbook reference where we need to clear the content
Dim Wb As Workbook
‘Set the reference for the workbook using the set key word
Set Wb = Workbooks(“Sales Report.xlsx”)
Wb.Worksheets(“Sales Summary”).Range(“A1:D10”).ClearContents
End Sub

The above code will delete the range A1 to D10 in the workbook Sales Report.xlsx and in the worksheet Sales Summary.

Loop Through all the Worksheets and Clear Contents of Specific Range

Assume we have 10 worksheets where we must get rid of the data in specific ranges across them. We do not have to write 10 different macros or code to clear content.

All we must do is to use loops to loop through each worksheet and clear the contents. Since we must loop through multiple worksheets, we must use the object loop, i.e., For Each loop.

For example, look at the following code.

Only Specific Range:

Sub Clear_Content_Each_Worksheet()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Range(“A1:G5”).ClearContents
Next Ws
End Sub

All Used Range: Sometimes in each worksheet the data range might be different. In such cases we cannot set the specified range, rather we must dynamically find the range in each worksheet.

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

The above code will loop through all the worksheets in the active workbook and clear contents from the ranges specified.

VBA clear contents multiple ranges: We can also clear contents from multiple ranges by referencing multiple ranges in nested range objects. For example, look at the following code.

We have multiple ranges data, i.e.,

• Range 1 = A1 to B4
• Range 2 = A7 to B10
• Range 3 = D1 to E4

Look at the following code.

Sub Clear_Content_MultipleRange()
Range(“A1:B4”, Range(“A7:B10”, Range(“D1:E4”))).ClearContents
End Sub

We have referenced multiple ranges using the RANGE object. Inside one Range object, we used two other Range objects to reference another range of cells; then, at the end of all the range objects’ parenthesis, we used the VBA Clear Contents method.

Once we execute this code, we will get the following result.

All the contents removed and only the formatting has been retained.

Important Things to Note

• VBA Clear Contents is available only when we reference the range.
• The VBA Delete method shifts all the cells below it upwards and all the right cells to the left after removing the referenced range.
• We can access clear contents using the Range object and CELLS property.
• Used Range means the filled range from minimum row to maximum row column.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the VBA Clear Content formula and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Clear Content in Excel. Here we learn how to clear contents from cells, specific ranges, or worksheet with examples & downloadable template. You can learn more from the following articles –

• VBA Return Function
• VBA Class Modules
• VBA Screen Update