Create an Excel Button to Clear Cells B7:B30 with VBA
Question details
The user needs to clear the contents of cells B7:B30 by clicking a button or triggering a macro from another cell.

- 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.
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.
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.
In Excel, press Alt+F11 to open the Visual Basic Editor. Select Insert > Module to create a standard code module.
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.
Close the VBA Editor, open the Developer tab, select Insert, and choose Button under Form Controls. Drag across the worksheet to draw the button.
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.
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.
Run the same macro from the Macros dialog
Use this option when you need to test the VBA procedure before adding a worksheet button.
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. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the compatible Excel workbook containing cells B7:B30.
- 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. 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. Save in XLSM format: Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) so the VBA project remains in the saved file.

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.




