VBA & Macro Problems

Create an Excel Button to Clear Cells B7:B30 with VBA

Natalie TaylorNatalie Taylor Nov 12, 2026 869 views

Question details

The user needs to clear the contents of cells B7:B30 by clicking a button or triggering a macro from another cell.

Create an Excel Button to Clear Cells B7:B30 with VBA
Product
Microsoft Excel desktop with VBA
Device & OS
not provided
Scenario
A worksheet requires a simple reset control that removes entered values from B7:B30 while keeping the existing cell formatting.
Observed behavior
No clear-range button or macro is currently configured.
Before you start

Confirm that the target worksheet uses cells B7:B30 for the values you want to remove, and save a copy of the workbook before testing the macro. Enable the Developer tab if it is not already visible.

Solution 1Recommended

Create a VBA macro and assign it to a Form Control button

Use this method when users should clear B7:B30 with one click while preserving cell formatting.

The ClearContents method removes values and formulas from the selected range but leaves formatting, borders, and column widths unchanged. The macro runs on the active worksheet, so place the button on the sheet containing B7:B30 or qualify the worksheet explicitly in the code.

1
Open the VBA editor

In Excel, press Alt+F11 to open the Visual Basic Editor. Select Insert > Module to create a standard code module.

2
Add the clear-range macro

Paste this code into the module: Sub ClearRange()\n Range("B7:B30").ClearContents\nEnd Sub. If the button may be clicked from another sheet, use Worksheets("Sheet1").Range("B7:B30").ClearContents and replace Sheet1 with the actual sheet name.

3
Insert a button

Close the VBA Editor, open the Developer tab, select Insert, and choose Button under Form Controls. Drag across the worksheet to draw the button.

4
Assign the macro

When the Assign Macro dialog appears, select ClearRange and click OK. Right-click the button and choose Edit Text if you want to change its label.

5
Save as a macro-enabled workbook

Select File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and click Save. If Excel displays a macro warning, choose to keep the VBA content.

Formatting remains intact: ClearContents deletes cell contents only. It does not remove formatting, comments, or data validation settings.

Clear a Spreadsheet Range with a VBA Button in WPS Office

WPS Office Spreadsheets can open compatible Excel macro-enabled workbooks and provide a familiar worksheet workflow for reviewing the VBA macro, assigning it to a form button, and saving the result in XLSM format on supported desktop editions.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the compatible Excel workbook containing cells B7:B30.
  2. 2. Open the VBA editor: Use the Tools or Developer menu to open the VBA editor, then choose Insert > Module and add the ClearRange procedure.
  3. 3. Insert and assign a button: Use Developer > Insert, select the Form Control button, draw it on the worksheet, and assign the ClearRange macro when prompted.
  4. 4. Save in XLSM format: Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) so the VBA project remains in the saved file.
Create a one-click control to clear B7:B30 while preserving formatting.Use a familiar ribbon, Developer tools, and macro workflow for spreadsheet automation.Open and save compatible Microsoft Excel XLS and XLSM workbooks for easier migration.Work with a lightweight Office suite for editing and sharing spreadsheet files.
microsoft office alternative - wps office

Frequently Asked Questions

Will this macro delete the formatting in B7:B30?

No. Range("B7:B30").ClearContents removes values and formulas only. Cell formatting, borders, and column widths remain unchanged.

How do I make the button clear a range on a specific worksheet?

Replace the unqualified range with a worksheet-qualified reference, such as Worksheets("Sheet1").Range("B7:B30").ClearContents. Change Sheet1 to the exact worksheet name.

Why does Excel show a macro security warning?

Excel may block VBA in files downloaded from the internet or stored in an untrusted location. Open the workbook from a trusted source, review the macro, and use Enable Content only when you trust the file.

What file format keeps the ClearRange macro?

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm). Saving as .xlsx removes the VBA project from the file.

Can I run the macro without inserting a button?

Yes. Press Alt+F8, select ClearRange, and click Run. You can also assign the macro to another supported control or shape.