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

Consider a dataset where you may want to reuse existing cells. You can use VBA code to clear the contents of an Excel file. Here’s an overview of our dataset, with sales records in several cities and countries.

Case 1 – Clear All Contents of aRange

Part 1 – Clear the Contents of a Specific Range of Cells

Steps:

• Go to the top ribbon and in the Developer tab, 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. From the top menu bar, press on Insert and select the Module option.

• A new Module window will appear. Paste this VBA code into the box.

Using Clear Command:

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

• Click on the Run option from the ribbon and select Run Sub/UserForm, or press F5 to run the code.

• The cells will be cleared and the formatting will be removed, but the empty cells will be there.

Using Delete Command:

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

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

The selected range of cells will be removed completely in this case.

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 while the Clear command removes only the cell values and the formatting.

Part 2 – Clear Contents of Full Worksheet

• Use the following code:

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

You can use the Delete command to remove all used cells in a worksheet.

• Use the code given below:

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

Part 3 – Clear the Contents of the Active Worksheet

• Use the following code:

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

Sub Delete_Contents_Range()
ActiveSheet.Cells.Delete
End Sub

Read More: How to Clear Contents of a Sheet with Excel VBA

Case 2 – Clear Contents of a Range but Keeping the Formatting

Part 1 – Contents of a Specific Range

• Use the following code and change your range if needed.

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

• When you run the code, you will see the selected cells are cleared, but the formatting remains.

Part 2 – Contents of a Specific Worksheet

• Use the following code and change the worksheet and range as necessary:

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

Part 3 – Contents from Other Workbooks

• You have to keep that workbook open.
• Use the following code:

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

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

• 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, 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.
• The ClearContents command will clear the values of the cells only and keep the cell formats untouched.

Download the Practice Workbook

Related Articles

• How to Clear Contents Without Deleting Formulas Using VBA in Excel
• Excel VBA to Clear Contents of Named Range
• Excel VBA to Delete and Shift Left Any Cell or Column
• How to Clear Cells in Excel with Button