Clear Function in Excel VBA: Add a Button to Clear Cells

VBA & macros · Updated November 18, 2026

To clear cells with a button in Excel VBA, use the ClearContents method in a macro, then assign that macro to a button. For example, a macro can clear a defined input range while keeping its formatting; use Clear instead only when you intend to remove cell formatting and other cell-level features too.

Is Clear a function in Excel VBA?

“Clear function” is a common way to describe this task, but Clear is a method of Excel’s Range object, not a worksheet function that you enter in a cell. A VBA macro calls a method on a range of cells, and a button gives users a convenient way to run that macro.

The key choice is what to remove. Range.ClearContents clears values and formulas but leaves the cells’ formatting in place. That usually suits an entry form: labels, borders, number formats, and fill colors remain, ready for the next set of entries. Range.Clear is broader. It clears the range rather than just its contents, so it can remove formatting and other cell-level settings as well. When the goal is only to reset data-entry cells, avoid using the broader method by default.

For example, a form might have labels in column A and user entries in B2:F100. Clearing only B2:F100 avoids removing the labels. A well-scoped macro also identifies the worksheet by name, rather than relying on whichever sheet happens to be active when someone clicks the button.

Choose the right clearing method

Pick the method based on what the next user of the worksheet should see. Clearing values, removing formatting, and resetting a worksheet are different jobs. Selecting the wrong method can make a form look broken or remove information users expect to keep.

  • ClearContents: Removes cell values and formulas from the specified range while retaining formatting. Use this for routine reset buttons on input forms.
  • Clear: Clears the specified range more completely, including its contents and formatting. Use it when the intention is to reset the cells themselves, not simply erase entries.
  • ClearFormats: Removes formatting while leaving cell contents in place. This is not the right choice for a button meant to erase submitted entries.

These methods act on the range in the macro; they do not automatically remove worksheet rows or columns. Also, clearing a range of cells is different from deleting a row: deleting a row shifts or removes worksheet structure and can affect formulas or references elsewhere. If the request is “empty the form,” use a clearing method on the input cells, not a row-deletion command.

A safe example macro

This example assumes the workbook contains a worksheet named Entry and that user-entered information belongs in B2:F100. It asks for confirmation before clearing and uses ClearContents so the range’s formatting stays in place. Put the code in a standard VBA module in the workbook that contains the Entry sheet.

Public Sub ClearEntryFields()
    Dim ws As Worksheet
    Dim answer As VbMsgBoxResult

    'Use the named sheet, not whichever sheet is active.
    Set ws = ThisWorkbook.Worksheets("Entry")

    'Default to No so an accidental click does not clear entries.
    answer = MsgBox("Clear the entry cells B2:F100?", _
                    vbYesNo + vbQuestion + vbDefaultButton2, _
                    "Confirm clear")

    If answer <> vbYes Then Exit Sub

    ws.Range("B2:F100").ClearContents
End Sub

Change the worksheet name and range to match your workbook. The example clears formulas as well as typed values inside B2:F100, so do not include cells that contain formulas you need to preserve. If you want to clear formats too, replace the final method with Clear only after checking that removing the range’s formatting and other settings is intended.

Before running any macro that changes cells, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and the confirmation prompt is a useful safeguard, not a substitute for a backup.

Add a button and assign the macro

In Excel for Microsoft 365 on Windows, add a Form Control button from the Developer tab. If Developer is not visible, enable it through File > Options > Customize Ribbon, select the Developer check box in the main tabs list, and choose OK.

  1. Open the worksheet where you want the button.
  2. Select Developer > Insert, then choose Button (Form Control) under Form Controls.
  3. Drag on the worksheet to draw the button. Excel opens the Assign Macro dialog.
  4. Select ClearEntryFields and choose OK.
  5. Right-click the button and choose Edit Text to give it a clear label, such as “Clear entries.”

Clicking the button runs the assigned macro. To change its assignment later, right-click it and choose Assign Macro. Keep the button outside the cells the macro clears. A Form Control button is a separate worksheet object, but placing it over the target range can make the sheet confusing to use.

Excel for Mac also supports VBA macros and worksheet Form Controls, although the Developer tab and dialog layout can differ from Windows. ActiveX controls are not supported on Mac, so a Form Control button is the more portable choice. Excel for the web cannot create or run VBA macros; open the workbook in the desktop Excel app to use this button.

Scope the range and protect important data

Think carefully about the exact cells before assigning a clear macro. A range that is too small leaves old entries behind; one that is too large can erase formulas, instructions, or results. For a simple form, write down which cells users may edit and include only those cells in the macro’s range. If input cells are separated, clear them individually in code rather than clearing the entire worksheet.

Using ThisWorkbook.Worksheets("Entry") makes the example target the sheet named Entry in the workbook containing the VBA. That is safer than using ActiveSheet, because users can switch tabs before clicking. If the worksheet is renamed or missing, the macro will not find it; update the name in the code when you change the sheet name.

Consider whether users should be able to clear data without a prompt. Confirmation is valuable when entries are difficult to recreate, but a prompt on a frequently used form can slow routine work. You can remove the prompt only if users understand the button’s effect and there is an appropriate way to recover information. A clear label should say what will be cleared, especially if the macro affects more than one range.

Common problems and what to check

  • The button does nothing: Confirm that the button is assigned to the intended macro and that macros are permitted for this workbook. Check that the macro is in a standard module and is declared as a public procedure without arguments.
  • The macro cannot find the sheet: Check the worksheet tab name against the name in the code. Spelling and spaces matter. Update the code if the tab has been renamed.
  • Excel says macros are disabled: Save the file in a macro-enabled format such as .xlsm, close and reopen it if needed, and follow your organization’s security policy for enabling macros. Do not enable macros in an untrusted workbook.
  • Some cells do not clear: Check whether the intended cells are actually inside the coded range and whether the worksheet is protected. A protected sheet may prevent edits to locked cells; review protection settings rather than broadening the macro range blindly.
  • The button disappears or moves: The control is a worksheet object, so its position can be affected by row or column changes and object settings. Reposition it and check its properties if the layout changes.

Also check whether the input range contains formulas. ClearContents removes formulas as well as fixed values, so a formula inside the range is not protected simply because it was not typed by a user. Keep calculated cells outside the clearing range or design the form so that user inputs and calculations occupy clearly separated areas.

Save, security, and workbook compatibility

Store a VBA workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its macro. Saving as a standard .xlsx file does not preserve VBA code; Excel warns that macro features will be removed if you continue in that format. Share the macro-enabled file with people who have desktop Excel and permission to run its macros.

Macros can make useful changes, but they can also alter or delete data. Only enable macros from a source you trust, and follow your workplace’s rules for downloaded or shared files. A clear-button macro is not a replacement for access controls or a backup. If the workbook contains important records, consider keeping a separate source copy or using a process that preserves submitted entries before clearing the form.

Form Control buttons are generally a practical choice for a small workbook because they can be assigned directly to a macro and are more suitable for cross-platform use than ActiveX controls. If you distribute the file, tell recipients that the button requires the desktop application and that the workbook must be saved in a macro-enabled format.

Alternatives to a VBA button

A macro button is useful when a workbook needs a repeatable, one-click reset. It may be unnecessary for a personal worksheet: selecting the input cells and pressing Delete clears their contents without adding a macro. In Excel for Microsoft 365 on Windows, Delete clears contents from selected cells; it does not remove their formatting. On Mac, the keyboard key may be labeled Delete or Backspace depending on the keyboard, so the ribbon’s Home > Clear > Clear Contents command is an alternative.

For users who need a clear action but cannot run VBA, a documented manual procedure or a separate blank template may be more appropriate. Excel for the web does not run VBA, so a VBA button is not a suitable solution for a workflow that must operate entirely in a browser. Choose the simplest option that fits how the workbook is used and where it will be opened.

Frequently Asked Questions

What is the difference between Clear and ClearContents in VBA?
ClearContents removes values and formulas while preserving formatting. Clear clears the range more broadly, including formatting. For a form reset, ClearContents is usually the safer starting point.

Can a button clear only certain cells?
Yes. The macro can target a specific range, such as B2:F100, or several carefully chosen ranges. Specify the worksheet and inspect the range so formulas and labels are not included by mistake.

Can I undo a VBA clear with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a copy first, and add a confirmation prompt if accidental clicks are a concern.

Why is my macro missing from the Assign Macro list?
Check that the procedure is in a standard module, is public, has no arguments, and is stored in a workbook available to Excel. Also confirm that the file is saved as .xlsm if it needs to retain the code.

Will the clear button work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac, with macros allowed, to use its VBA button.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.