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

This is the sample dataset.

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.

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

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

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

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

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

Create a macro button:

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

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

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

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

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

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

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

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

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

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

• The selected cells are cleared.

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

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

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

Download Practice Workbook

Download the practice workbook here:

Related Articles

• How to Clear Contents Without Deleting Formulas Using VBA in Excel
• Excel VBA to Clear Contents of Named Range
• Excel VBA to Delete and Shift Left Any Cell or Column
• Excel VBA: Clear Contents If Cell Contains Specific Values