[Excel VBA] How to Clear Specific Cells with a Button

Background

When working in Excel, you often need to reset or clear the contents of specific cells where you have entered data.

I wanted to create a tool that allows me to “delete text from specific cells only when a button is pressed.” In this article, I will share the VBA code to implement this feature.

The Goal

I want to achieve the following actions with a single button click:

1. Delete the value in cell A1.
2. Delete the values in the range B1 to B10.
3. Delete all values in Column C.

The VBA Code

Below is the code designed to be registered to a command button or a shape on your worksheet.

Private Sub CommandButton_Delete_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet  ' Use the currently active sheet

    ' Clear content of cell A1
    ws.Range("A1").ClearContents

    ' Clear content of range B1:B10
    ws.Range("B1:B10").ClearContents

    ' Clear content of the entire Column C
    ws.Range("C:C").ClearContents
End Sub

Note: This macro uses .ClearContents. This means it deletes only the text/values inside the cells. The cell formatting (background colors, borders, font styles) will remain unchanged.

Explanation of the Code

• ClearContents: This is a VBA method that removes only the “value” of the cell. It does not delete the cell itself or its formatting.
• ws.Range("C:C"): By specifying the column letter like this, you can clear the entire column.
• Set ws = ActiveSheet: This ensures the code runs on the sheet you are currently looking at.

How to Link the Macro to a Button

1. Go to the Insert tab in the Excel ribbon and select Shapes.
2. Place a shape (like a rectangle) on the sheet to act as your button.
3. Right-click the shape and select Assign Macro.
4. Select the macro name (e.g., CommandButton_Delete_Click) and click OK.
5. Now, simply click the shape to clear the specified cells.

Summary

• Use ClearContents when you want to batch delete only the text in specific cells.
• By linking the macro to a button or shape, you can reset your form with one click.
• This is highly useful for creating templates or input forms where you need to clear old data but keep the layout and design.