Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA to Clear Contents of Range (3 Suitable Cases)

If you are searching for the solution or some special tricks to clear contents of range with Excel VBA then you have landed in the right place. There are some easy steps to clear the contents of the range with Excel VBA. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.


Download Practice Workbook

You can download the practice workbook from here:


Clear Contents of Range with Excel VBA: 3 Cases

Suppose, you have a dataset that you may want to reuse clearing existing cells. So, you can use VBA code to clear the contents of an Excel file. In this section, I will show you the quick and easy methods to clear contents of range with Excel VBA on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

Clear Contents of Range with Excel VBA


Case 1: Clear All Contents of Range

You can use both the Clear command and the Delete Command to clear contents of a specific range in the current worksheet or other worksheets even from other workbooks also. I will show you all the cases one by one.

i. Clear Contents of Specific Range of Cells

If you want to clear the contents of a specific range then follow the steps below:

📌 Steps:

  • For this, first, go to the top ribbon and press on the Developer, then press on the Visual Basic option from the menu.
  • You can use ALT + F11 to open the “Microsoft Visual Basic for Applications” window if you don’t have the Developer tab added.

Microsoft Visual Basic for Applications

  • Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.

insert module

  • Now, a new “Module” window will appear. And Paste this VBA code into the box.

Using Clear Command:

Sub Clear_Contents_Range()
Range("B4:D5").Clear
End Sub

VBA Code Using Clear Command

  • To run the code go to the top menu, press on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.

Run VBA Code

  • Using the Clear command, the cells will be cleared also the formatting will also be removed. But the empty cells will be there.

Using Contents of Range Using VBA


Using Delete Command:

You can also use the Delete command instead of the Clear command. Then paste the following code in the module.

Sub Delete_Contents_Range()
Range("B4:D5").Delete
End Sub

Using Delete Command

Using the Delete command, when you run the code, the selected range of cells will be removed completely.

Using Delete Command

Read More: Excel VBA: Clear Contents If Cell Contains Specific Values


Difference Between Clear and Delete Command in Excel VBA:

The key difference between the Clear and Delete commands in Excel VBA is that the Delete command removes the selected range of cells completely and the Clear command removes only the cell value and the formatting but the empty cell will be there.


ii. Clear Contents of Full Worksheet

If you want to clear all cells of a worksheet then use this code given below. Here, I am clearing contents from the worksheet named “1.2”. You have to insert the name of the worksheet that you want to clear in the inverted commas.

Sub Delete_Contents_Range()
Worksheets("1.2").Cells.Clear
End Sub

Clear Contents of Full Worksheet

Also, you can use the Delete command to remove all used cells in a worksheet. Then, you have to use the code given below. Just, change the name of the worksheet in the inverted commas.

Sub Delete_Contents_Range()
Worksheets("1.2").Cells.Delete
End Sub

iii. Clear Contents of Active Worksheet

Sometimes, you may need to clear the contents of the active worksheet only. In this case, you have to a simple code given below:

Sub Delete_Contents_Range()
ActiveSheet.Cells.Clear
End Sub

Clear Contents of Active Worksheet

You can also use the delete command to remove the cells completely from the active worksheet. for this, paste the following code into the module.

Sub Delete_Contents_Range()
ActiveSheet.Cells.Delete
End Sub

Read More: Difference Between Delete and Clear Contents in Excel


Case 2: Clear Contents of Range Keeping the Formatting

In the previous methods, you have noticed that you are removing the cell values along the cell formattings also. So, if you want to remove the cell values only while keeping the formatting same.

i. Contents of Specific Range

You can use the ClearContents command in Excel VBA to clear the contents of a specific range. For this, paste the following code into the module.

Sub Delete_cell_Keeping_format()
Range("B2:D4").ClearContents
End Sub

VBA Code of Clear Contents of Range Keeping the Formatting

As a result, when you run the code you will see the selected cells are cleared but the formats are still remaining.

Output of Clear Contents of Range Keeping the Formatting

Read More: How to Clear Cells with Certain Value in Excel (2 Ways)


ii. Contents of Specific Worksheet

When you want to clear the contents of the specific worksheet while keeping the formats the same, you have to use the following code below:

Sub Delete_Worksheet_Cells_Keeping_format()
Worksheets("2.2").Range("B2:D4").ClearContents
End Sub

VBA Code of Clearing Contents of Specific Worksheet

🔎 VBA Code Explanation:

  • Worksheets(“2.2”) is calling the worksheet named “2.2” if you want to call a worksheet of any other name then, insert the name of the worksheet in the inverted commas.
  • And, Range(“B2:D4”) is specifying the range of cells that will be cleared. You can edit the cell range as to your need.

iii. Contents from Other Workbook

You can also clear the contents of another workbook using VBA code. But, for this, you have to keep that workbook open. Use the following code below for this:

Sub Delete_Other_Workbook_Cells_Keeping_format()
Workbooks("file 1").Worksheets("Sheet1").Range("B3:D12").ClearContents
End Sub

VBA Code of Clearing Contents from Other Workbook

🔎 VBA Code Explanation:

  • Workbooks(“file 1”) is calling the workbook named “File 1”. While using your workbooks, you have to insert the name of the workbook in the inverted commas.
  • Worksheets(“Sheet1”) is calling the worksheet named “Sheet 1”. To call any other worksheet, just insert the name of the worksheet instead of “Sheet 1” in this code.
  • And, Range(“B3:D13”) is specifying the range of cells that will be cleared. You can edit the cell range as of your need.

Read More: How to Clear Contents Without Deleting Formulas Using VBA in Excel


Case 3: Clear Contents of Specific Range of All Worksheets at Once

If you need to clear the contents of a specific range of multiple worksheets you can do it at once using a VBA code. You have to use For loop to do this task. Paste this code into the module to clear the contents of range B2:D4 of all worksheets. If you want to change the cell range then edit the 4th line and insert your data range.

Sub Clear_Specific_Range_All_Worksheets()
Dim W_S As Worksheet
For Each W_S In ActiveWorkbook.Worksheets
W_S.Range("B2:D4").ClearContents
Next W_S
End Sub

VBA Code of Clearing Contents of Specific Range of All Worksheets at Once


Things to Remember

  • Using the Clear command will only clear the cell values and formats.
  • Delete command will remove the cells completely.
  • ClearContents command will clear the values of the cells only and keep the cell formats untouched.

Conclusion

In this article, you have found how to clear the contents of range with Excel VBA. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo