How to Clear Cells in Excel VBA (9 Easy Methods)

The following dataset has the Product and Sales columns. We will clear some cells with VBA.

Method 1 – Using the VBA Clear Command in Excel to Clear a Single Cell

We will clear cell C9 using the Clear method.

Step 1 – Inserting a Module

• Go to the Developer tab and select Visual Basic.

This will bring out the VBA editor window. You can press Alt + F11 to bring out the VBA editor window.

• From the Insert tab, select Module.

Here’s the Module.

Step 2 – Using Code to Clear Cells in Excel

• Insert the following code in the Module.

Sub clear_Single_cell()
    Range("C9").Clear
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• Go to the Developer tab and select Macros.

A Macro dialog box will appear. You can press Alt + F8 to bring out the Macro dialog box.

• Select clear_Single_cell from Macro name.
• Click on Run.

This clears cell C9 as per the code.

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

Method 2 – Clearing Range of Cells with Excel VBA

We will use the Clear method in VBA to clear cells B7:C9, which are marked with green color.

Steps:

• Followed Step 1 of Method 1 to get a Module.
• Insert the following code in the Module.

Sub clear_Range_of_cell()
     Range("B7:C9").Clear
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• Go to the Developer tab and select Macros.

A Macro dialog box will appear. You can press Alt + F8 to bring out the Macro dialog box.

• Select clear_Range_of_cells from Macro name.
• Click on Run.

You can see cells B7:C9 have been cleared in Excel VBA.

Read More: Excel VBA to Clear Contents of Range

Method 3 – Clearing Cells from the Selection in Excel

We will use the Clear method in VBA to clear selected cells.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub Clear_Cells_from_Selection()
     Selection.Clear
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• Select the cells you want to clear. We selected cells B7:C9.

• Go to the Developer tab and select Macros.

A Macro dialog box will appear. You can press Alt + F8 to bring out the Macro dialog box.

• Select Clear_Cells_from_Selection from Macro name.
• Click on Run.

You can see the selected cells B7:C9 have been cleared in Excel VBA.

Read More: How to Clear Cells with Certain Value in Excel

Method 4 – Applying the VBA ClearContents Command to Clear Cell Data in Excel

We will use the ClearContents method in VBA to clear the contents in cell B7:C9.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub clear_Content_of_cells()
     Range("B7:C9").ClearContents
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• To Run the code, go to the Developer tab and select Macros.

A Macro dialog box will appear. You can press Alt + F8 to bring out the Macro dialog box.

• Select clear_Content_of_cells from Macro name.
• Click on Run.

You can see the contents of cells B7:C9 have been cleared in Excel VBA. However, the format in the cells is still present.

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

Method 5 – Using the VBA ClearFormats Statement to Clear Cell Formatting

We will use the ClearFormats method in VBA to clear the formatting of the cells B5:C9.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub clear_Formates_of_cells()
     Range("B5:C9").ClearFormats
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• To Run the code, go to the Developer tab and select Macros.

A Macro dialog box will appear. You can press Alt + F8 to bring out the Macro dialog box.

• Select Clear_Formates_of_cells from Macro name.
• Click on Run.

Here’s the result.

Read More: How to Clear Contents in Excel Without Deleting Formatting

Method 6 – Using VBA to Clear Comments of Cells in Excel

You can see two comments in cells C8 and C8 respectively. We will use the ClearComments method in VBA to clear the comments of the cells.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub clear_Comments_from_cells()
    Range("B5:C9").ClearComments
End Sub

• Save the code and close the VBA editor window.
• Go back to the worksheet.
• Run the code by going to Macros or pressing Alt + F8.

• Select clear_Comments_from_cells from Macro name.
• Click on Run.

There are no comments in the dataset.

Read More: How to Clear Cell Contents Based on Condition in Excel

Method 7 – Inserting the VBA ClearHyperlinks Command to Clear Hyperlinks from Cells

Cells B5:B9 contains Sheet Names. All these Sheet Names have Hyperlinks. We will use the ClearHyperlinks method in VBA to clear Hyperlinks from cells.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub clear_Hyperlinks_from_cells()
     Range("B5:B9").ClearHyperlinks
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• To Run the code, go to the Developer tab and select Macros or press Alt + F8.

• Select Clear_Hyperlinks_from_cells from Macro name.
• Click on Run.

• The hyperlinks no longer work.

Method 8 – Clearing Cell Color with Excel VBA

Cells B5:C9 have a green color. We will clear cell color in Excel VBA.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub clear_Color_from_cells()

     Dim cell_range As Range
     Set cell_range = Range("B5:C9")
     cell_range.Interior.Color = xlColorIndexNone

End Sub

• Save the code and close the VBA editor window.
• Go back to the worksheet.
• To Run the code, go to the Developer tab and select Macros or press Alt + F8.

A Macro dialog box will appear.

• Select Clear_Color_from_cells from Macro name.
• Click on Run.

You can see there is no color in the cells B5:C9 of the dataset.

Method 9 – Use VBA to Clear All Cells from theWorksheet in Excel

We will use the Clear method in VBA to clear all cells from the worksheet.

Steps:

• Follow Step 1 of Method 1 to bring out the Module.
• Insert the following code in the Module.

Sub clear_all_cells_from_worksheet()
     Worksheets("Clear All Cells ").Cells.Clear
End Sub

• Save the code and close the VBA editor window.
• Go back to the workbook.
• To Run the code, go to the Developer tab and select Macros or press Alt + F8.

A Macro dialog box will appear.

• Select Clear_all_cells_from_worksheet from Macro name.
• Click on Run.

You’ll get an empty sheet.

Download the Practice Workbook

Related Articles

• How to Clear Multiple Cells in Excel
• How to Clear Recent Documents in Excel
• How to Clear Excel Temp Files
• Excel VBA to Clear Contents of Named Range
• How to Clear Contents of a Sheet with Excel VBA