Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Download Practice Workbook

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


9 Methods to Clear Cells in Excel VBA

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.

How to Clear Cells in Excel VBA


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.

Using Clear Methods in Excel VBA

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

VBA code to Clear Single Cell in Excel

Code Breakdown

  • We declare clear_single_cell as our Sub.
  • The Clear method is used to clear cell C9.
  • 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 in Excel

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

Clearing Range of Cells 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.
  • Moreover, we type the following code in the Module.
Sub clear_Range_of_cell()
     Range("B7:C9").Clear
End Sub

Code Breakdown

  • We declare clear_Range_of_cell as our Sub.
  • The Clear method is used to clear cells B7:C9.
  • 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.

Range of Cell Cleared in Excel VBA

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


3. Clear 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

VBA Code to Clear Cells from Selection

Code Breakdown

  • We declare Clear_Cells_from_Selection as our Sub.
  • The Clear method is used to clear the selected cells.
  • 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.

selecting Clear cells from selection from Macro Name to run code in Excel VBA

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 (2 Ways)


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

Applying Clear Contents Method in VBA to Clear Cell Content

Code Breakdown

  • We declare clear_Content_of_cells as our Sub.
  • The ClearContents method is used to clear the cell content of cells B7:C9.
  • 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. Employing VBA ClearFormats Statement in Excel

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

Use of Clear Formats Method in Excel VBA to Clear Formats of Cells

Code Breakdown

  • We declare clear_Formates_of_cells as our Sub.
  • The ClearFormats method is used to clear the Formats of cells B5:C9.

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

Use of Clear Comments in VBA to Clear Comments

Code Breakdown

  • We declare clear_Comments_from_cells as our Sub.
  • The ClearComments method is used to clear the Comments of cells B5:C9.
  • 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.


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

Inserting Clear Hyperlinks in Excel VBA to Clear Cell Hyperlinks

Code Breakdown

  • We declare clear_Hyperlinks_from_cells as our Sub.
  • The ClearHyperlinks method is used to clear the Hyperlinks of cells B5:B9.
  •  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.

Cleared Hyperlinks in Cell by Employing Excel VBA


8. Clearing Cell Color in Excel

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.

Cell Color Clearing by Use of 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

Excel VBA to Clear Color of Cells

Code Breakdown

  • We declare clear_Color_from_cells as our Sub.
  • We take cell_range as Range.
  • We set the interior color of cells B5:C9 as xlColorIndexNone.
  • 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.


9. 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.

Clearing All Cells by Inserting Excel VBA

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

Using VBA to Clear All Cells in Excel Workbook

Code Breakdown

  • We declare clear_all_cells_from_worksheet as our Sub.
  • The Clear method is used to clear all the cells of the Clear All Cells
  • 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.


Practice Section

You can download the above Excel file to practice the explained methods.


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. Please visit our website Exceldemy to explore more.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo