Osman Goni Ridwan
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.
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 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.
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.
- 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 in 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.
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
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 SubRead More: How to Clear Contents of a Sheet with Excel VBA
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
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 formatting. So, if you want to remove the cell values only while keeping the 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.
Read More: How to Clear Cells with Certain Value in Excel
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”) 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.
Read More: Excel VBA to Clear Contents of Named Range
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 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
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:
Conclusion
In this article, you have found how to clear the contents of 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.
