How to Clear Cells in Excel using a Button – 4 Steps

This is the sample dataset.

How to Clear Cells in Excel with Button


Step 1 – Creating a VBA Module to Clear Cells in Excel

  • Go to the Developer tab and click Visual Basic.

You can also press ALT + F11 to open the “Microsoft Visual Basic for Applications” window.

Open Visual Basic to Clear Cells in Excel with Button

  • In “Insert”, select “Module’”.

Create VBA Module to Clear Cells in Excel with Button


Step 2 – Inserting a VBA Code in the Module

  • In the“Module” window, enter this VBA code.

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 

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: Excel VBA to Clear Contents of Range


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

The ClearContent command only clears the cell value while keeping the cell format. The Clear command removes both cell value and cell formatting and leaves the cells blank. The Delete command removes the cells and shifts the bottom cells up.


Step 3 – Create a Button to Clear Cells in Excel

  • Go to the Developer tab.
  • Click Insert and select Button.

Create Macro Button

  • Draw a box in the area you want to place the button.

Draw box for Macro Button

  • The  “Assign macro” window will open.
  • Select the macro you created.
  • Click OK.

assign macro

  • You will see a macro button in the selected area. Right-click the macro button to rename it as “Clear Contents Only”.

Macro button to Clear Cells in Excel

  • Create two more buttons for the other codes named “Clear Cells Including Format” and “Delete Cells

How to Clear Cells in Excel with Button


Step 4 – Running the VBA Macro to Clear Cells with a Button

  • Click “Clear Contents Only” to clear the selected cell values only.

Click button to use ClearContents VBA

  • A window will open. Select the range and click OK.

Select range to clear

  • The selected cell values are cleared but the format is the same.

ClearContents Command Output


 

  • Click “Clear Cells Including Format” to clear cell values and remove formatting.
  • Select the cell range that you want to clear and click OK.

Use Clear Cells Including Format Button

The selected cells are cleared.

Clear Command Output


  • Click “Delete Cells” and select the cell range.

Use Delete Cells Button

The selected cells are removed and the bottom cells are shifted up.

Delete Command Output

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


Download Practice Workbook

Download the practice workbook here:


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

2 Comments
  1. should select the cell range that i want to clear in the input box every time ?

    • Hello Alami,

      Yes. you need to select the cell in the InputBox every time you want to clear the cells.
      But if you want to avoid using InputBox, I’m giving you two different code to do so.

      Mentioned the range then clear cells:

      Sub Clear_Range()
          Sheets("Sheet1").Range("A1:B10").Clear
      End Sub
      

      Select the cells then run the VBA code:

      Sub Clear_from_Selection()
          Selection.ClearContents
      End Sub
      

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo