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

If you are searching for a 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.

Excel VBA to Clear Contents of Range: 3 Suitable 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 the 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 the Microsoft 365 version here. But you can use any other versions as of your availability. If anything in this article doesn’t work in your version then leave us a comment.

Case 1: Clear All Contents of Range

You can use both the Clear command and the Delete Command to clear the 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.

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.

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

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.

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

Using Delete Command:

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

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

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

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

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

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

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 formatting. So, if you want to clear contents without deleting formatting formatting, you can follow the below sections.

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

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

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 Explanation:

Worksheets(“2.2”) calls 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 the 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 Explanation:

Workbooks(“file 1”) calls the workbook named “File 1”. While using your workbooks, you have to insert the name of the workbook in the inverted commas.
Worksheets(“Sheet1”) calls 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 your need.

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

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.

Download Practice Workbook

You can download the practice workbook from here:

Clear Contents of Range.xlsm

Conclusion

In this article, you have found how to clear the contents of the range with Excel VBA. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Get FREE Advanced Excel Exercises with Solutions!