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

If you want to clear cells in Excel VBA, you have come to the right place. Here, we will walk you through 9 easy methods to do the task smoothly.

The following dataset has the Product and Sales columns. Using this dataset, we will go through 9 easy methods to clear cells in Excel VBA.

Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.

1. Using VBA Clear Command in Excel to Clear Single Cell

The Clear method in VBA is helpful to clear both the formats and content of a cell or a range of cells.

In this method, we will clear cell C9 using the Clear method.

Let’s go through the following steps to do the task.

Step-1: Inserting Module

In this step, we will bring out the Module to type the VBA code.

• First of all, we will go to the Developer tab >> select Visual Basic.

This will bring out the VBA editor window.

Here, you can press ALT+F11 to bring out the VBA editor window.

At this point, a VBA editor window will appear.

• Furthermore, from the Insert tab >> select Module.

As a result, you can see the Module.

Step-2: Using Code to Clear Cells in Excel

In this step, we will insert VBA code into the Module.

• In the beginning, we will type the following code in the Module.

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

• After that, we will Save the code >> Close the VBA editor window.
• Moreover, we will go back to our workbook.

Afterward, we will Run the code.

• To Run the code, we will go to the Developer tab >> select Macros.

At this moment, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Moreover, we will select clear_Single_cell  from the Macro name.
• In addition, click on Run.

As a result, you can see cell C9 contains nothing.

Hence, we clear cell C9 in Excel VBA.

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

2. Clearing Range of Cells with Excel VBA

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

Steps:

Let’s go through the following steps to do the task.

• First, we followed Step-1 of Method-1 to bring out the Module.
• Moreover, we type the following code in the Module.

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

• Furthermore, we will Save the code >> Close the VBA editor window.
• Moreover, we will go back to our workbook.

Next, we will Run the code.

• To Run the code, we will go to the Developer tab >> select Macros.

At this point, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Moreover, we will select clear_Range_of_cells  from the Macro name.
• Then, click on Run.

Therefore, you can see cells B7:C9 have been cleared in Excel VBA.

Read More: Excel VBA to Clear Contents of Range

3. Clearing Cells from Selection in Excel

In this method, we will use the Clear method in VBA to clear cells from the selection. This method is helpful as we can select cells according to our preference.

Steps:

Let’s go through the following steps to do the task.

• In the beginning, we followed Step-1 of Method-1 to bring out the Module.
• Afterward, we type the following code in the Module.

Sub Clear_Cells_from_Selection()
     Selection.Clear
End Sub

• Furthermore, we will Save the code >> Close the VBA editor window.
• After that, we will go back to our workbook.

At this point, we have to select the cells that we want to clear.

• Here, we selected cells B7:C9.
• Afterward, to Run the code, we will go to the Developer tab >> select Macros.

Next, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Moreover, we will select Clear_Cells_from_Selection  from the Macro name.
• In addition, click on Run.

Then, 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

4. Applying VBA ClearContents Command to Clear Cell Data in Excel

In this method, we will use the ClearContents method in VBA to clear the contents in cell B7:C9.

The ClearContents method is helpful to clear only the cell data or cell content. Therefore, when we want to only clear the cell content and we want that the cell format remains the same, the ClearContents method is helpful.

Steps:

Let’s go through the following steps to do the task.

• First of all, we followed Step-1 of Method-1 to bring out the Module.
• Then, we type the following code in the Module.

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

• Furthermore, we will Save the code >> Close the VBA editor window.
• Moreover, we will go back to our workbook.

Next, we will Run the code.

• To Run the code, we will go to the Developer tab >> select Macros.

At this point, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Moreover, we will select clear_Content_of_cells from the Macro name.
• Then, click on Run.

Hence, 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

5. Using VBA ClearFormats Statement to Clear Cells

In this method, we will use the ClearFormats method in VBA to clear the formats of the cells B5:C9.

The ClearFormats method is helpful to get rid of the Formats of the cells, along with that the cell contents remain the same in this case.

Steps:

Let’s go through the following steps to do the task.

• First, we followed Step-1 of Method-1 to bring out the Module.
• Afterward, we type the following code in the Module.

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

Furthermore, we will Save the code >> Close the VBA editor window.

• Then, we will go back to our workbook.
• Afterward, to Run the code, we will go to the Developer tab >> select Macros.

Next, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Moreover, we will select Clear_Formates_of_cells from the Macro name.
• Along with that, click on Run.

Therefore, you can see the cells of the dataset have no formats.

Hence, we can clear the formats of cells in Excel VBA.

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

6. Using VBA to Clear Comments of Cell in Excel

In the following dataset, you can see two comments in cells C8 and C8 respectively.

In this method, we will use the ClearComments method in VBA to clear the comments of the cells.

Steps:

Let’s go through the following steps to do the task.

• First of all, we followed Step-1 of Method-1 to bring out the Module.
• Next, we type the following code in the Module.

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

• Furthermore, we will Save the code >> Close the VBA editor window.

Moreover, we will go back to our worksheet.

Next, we will Run the code.

• To Run the code, we will go to the Developer tab >> select Macros.

Next, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Furthermore, we will select clear_Comments_from_cells from the Macro name.
• Along with that, click on Run.

Hence, you can see there is no comment in the dataset.

Therefore, we clear comments from cells in Excel VBA.

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

7. Inserting VBA ClearHyperlinks Command to Clear Hyperlinks from Cells

Here, you can see in the following picture that cells B5:B9 contains Sheet Name. All these Sheet Names have Hyperlinks.

In this method, we will use the ClearHyperlinks method in VBA to clear Hyperlinks from cells.

Let us first show you how these hyperlinks work.

• First, we will click on the Hyperlink of cell B5

Then, you can see the Clearing Sigle Cell sheet has opened.

Furthermore, we will clear these Hyperlinks.

Steps:

Let’s go through the following steps to do the task.

• In the beginning, we followed Step-1 of Method-1 to bring out the Module.
• Then, we type the following code in the Module.

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

•  Afterward, we will Save the code >> Close the VBA editor window.
• After that, we will go back to our workbook.
• Moreover, to Run the code, we will go to the Developer tab >> select Macros.

At this point, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• In addition, we will select Clear_Hyperlinks_from_cells from the Macro name.
• Along with that, click on Run.

• After that, we will click on the Hyperlink of cell B5.

You will notice that the Hyperlink does not work this time as we do not automatically go to the Clearing Single Cell sheet.

Therefore, we clear Hyperlinks from cells in Excel VBA.

8. Clearing Cell Color with Excel VBA

In the following picture, you can see cells B5:C9 have a green color. In this method, we will clear cell color in Excel VBA.

Steps:

Let’s go through the following steps to do the task.

• First, we followed Step-1 of Method-1 to bring out the Module.
• Afterward, we type 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

• Furthermore, we will Save the code >> Close the VBA editor window.
• Moreover, we will go back to our worksheet.
• Next, to Run the code, we will go to the Developer tab >> select Macros.

Later a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• Moreover, we will select Clear_Color_from_cells from the Macro name.
• Along with that, click on Run.

Therefore, you can see there is no color in the cells B5:C9 of the dataset.

Read More: How to Remove Formatting in Excel Without Removing Contents

9. Use of VBA to Clear All Cells from Worksheet in Excel

In this method, we will use the Clear method in VBA to clear all cells from the worksheet. This is a handy method to get rid of all the cells at once.

Steps:

Let’s go through the following steps to do the task.

• In the beginning, we followed Step-1 of Method-1 to bring out the Module.
• Later, we type the following code in the Module.

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

• At this point, we will Save the code >> Close the VBA editor window.
• After that, we will go back to our workbook.
• Moreover, to Run the code, we will go to the Developer tab >> select Macros.

At this moment, a Macro dialog box will appear.

Here, you can press ALT+F8 to bring out the Macro dialog box.

• In addition, we will select Clear_all_cells_from_worksheet from the Macro name.
• Furthermore, click on Run.

Therefore, you can see an empty worksheet.

Hence, we can clear all cells from the worksheet in Excel VBA.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Conclusion

Here, we tried to show you 9 easy methods to clear cells in Excel VBA. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

Related Articles

• How to Clear Formatting in Excel
• How to Clear Multiple Cells in Excel
• Difference Between Delete and Clear Contents 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