How to Clear Cells in Excel with Button (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

If you are searching for the solution or some special tricks to clear cells in Excel with a button then you have landed in the right place. There are some easy steps to clear cells in Excel with a button. 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.


Download Practice Workbook

You can download the practice workbook from here:


Steps to Clear Cells in Excel with Button

In this section, I will show you the quick and easy steps to clear cells in Excel with a button on 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.

How to Clear Cells in Excel with Button


📌 Step 1: Create VBA Module

  • For this, first, go to the top ribbon and press on the Developer,  and 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.

Open Visual Basic to Clear Cells in Excel with Button

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

Create VBA Module to Clear Cells in Excel with Button


📌 Step 2: Insert VBA Code in Module

  • Now, a new “Module” window will appear. And Paste this VBA code into the box.

✅ Code For Clearing Cells Keeping the Format:

Sub Clear_Cell_With_Button()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select the Range of Cell", Title:="Exceldemy", Type:=8)
rng.Select
Selection.ClearContents
End Sub

VBA Code to Clear Cells in Excel with Button


 Code For Clearing Cells including Format

Sub Clear_Cell_With_Button_including_Format()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select the Range of Cell", Title:="Exceldemy", Type:=8)
rng.Select
Selection.Clear
End Sub

VBA Code to Clear Cells in Excel with Button


 Code For Deleting Cells Completely

Sub Delete_Cell_With_Button()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select the Range of Cell", Title:="Exceldemy", Type:=8)
rng.Select
Selection.Delete
End Sub

VBA Code For Deleting Cells Completely

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


📣 Difference Between Clear, Delete, and ClearContents Command in Excel VBA

You have 3 commands available in Excel VBA to clear cells. But they do works differently for different purposes. The ClearContent command only clears the cell value while keeping the cell format the same. And, the Clear command removes both the cell value and cell formatting and leaves the cells blank. But the Delete command removes the cells completely and shifts the bottom cells up to fill the place.


📌 Step 3: Create Macro Button

You can also create a macro button, so you can easily use this worksheet repeatedly. To create a macro button follow the steps below:

  • First, go to the Developer tab in the top ribbon.
  • Then, click on the Insert options and select the Button

Create Macro Button

  • After selecting the button icon, you have to draw a box in the region where you want to place the button. So draw a box in a suitable region to create a macro button.

Draw box for Macro Button

  • After drawing the box, a window named “Assign macro” will appear.
  • From the list, select the macro you have created before.
  • Then, press OK.

assign macro

  • As a result, you will see a macro button will be created in the selected region. Right-click on the macro button to rename it as “Clear Contents Only”.

Macro button to Clear Cells in Excel

  • Now, create two more buttons for other codes named “Clear Cells Including Format” and “Delete Cells

How to Clear Cells in Excel with Button

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


📌 Step 4: Run Macro with Button

Now, try to run the Macro codes with the created buttons one by one. I am showing here the output for each command.

🎉 Clear Contents Command Output

  • Now, click on the button named “Clear Contents Only” to clear the selected cell values only.

Click button to use ClearContents VBA

  • After clicking the button, there will open a pop-up window that will ask you to select the range of cells that you want clear.
  • So, select the range of cells in the box and press OK.

Select range to clear

  • After clicking OK, you will see that the selected cell values are cleared but the format of the cells is still remaining the same.

ClearContents Command Output

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


🎉 Clear Command Output

  • Now, similarly, click on the “Clear Cells Including Format” to clear cell values and remove the formattings also.
  • As shown before, specify the cell range that you want to clear in the input box and press OK.

Use Clear Cells Including Format Button

  • Now, you will see that the selected cells are cleared completely. Both cell values and cell formattings are cleared in this case.

Clear Command Output


🎉 Delete Command Output

  • Now, for the case of using the Delete command, you have to use the same steps to run the macro.
  • So, click on the button named “Delete Cells” and specify the cell range in the input box.

Use Delete Cells Button

  • For this case, you will see that the selected cells are removed completely and the bottom cells are shifted up.

Delete Command Output

Read More: Difference Between Delete and Clear Contents in Excel


Things to Remember

  • Clear command clears both the cell values and cell formats.
  • Delete command removes the cells completely.
  • ClearContents command clears only the values of the cells and keeps the cell formats the same.

Conclusion

In this article, you have found how to clear cells in Excel with the button. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo