In Excel, a clear-contents macro removes cell values and formulas while leaving cell formatting in place. In Microsoft 365 for Windows, the core VBA command is Range.ClearContents; use a specific, worksheet-qualified range so the macro clears only the cells you intend.
What a Clear Contents Macro Does
Excel’s Clear Contents command removes what is stored in cells—their typed values and formulas—without deleting the cells themselves. A VBA macro can call the same operation on a chosen range, which is useful when a form or template needs to be reset repeatedly. For example, a workbook might have an “Entry” sheet where staff enter names and quantities, while headings, instructions, borders, and number formats should remain ready for the next entry.
Clearing contents is different from deleting cells. It does not shift neighboring cells to fill a gap, and it does not remove the cell’s basic formatting. It is also not the same as clearing everything: formats and other cell features can remain. Most importantly, formulas are contents too. If a formula lies inside the selected range, ClearContents removes it just as it removes a typed number or text.
That last point is why specifying the target matters more than the shortness of the code. A macro that clears an entire worksheet may erase calculations as well as user input. A narrowly defined range is easier to understand, review, and protect against accidental data loss.
VBA Macro for Clearing a Fixed Input Range
This example clears two input blocks on a worksheet named Entry. It displays a confirmation before making the change and leaves the rest of the sheet untouched. Change the sheet name and cell addresses to match your workbook before running it.
Sub ClearEntryContents()
' Clear only the designated input cells on the Entry sheet.
Dim ws As Worksheet
Dim answer As VbMsgBoxResult
Set ws = ThisWorkbook.Worksheets("Entry")
answer = MsgBox("Clear the contents of the entry cells?", _
vbYesNo + vbQuestion, "Confirm clear")
If answer = vbYes Then
ws.Range("B3:B12,D3:D12").ClearContents
End If
End Sub
The range expression B3:B12,D3:D12 refers to two separate blocks: B3 through B12 and D3 through D12. It does not include column C or other cells. The ThisWorkbook reference means the workbook containing this macro, and the explicit ws variable ensures the range is on the Entry sheet rather than whichever sheet happens to be active. The sheet tab must be named exactly Entry or the code will stop with a “Subscript out of range” error.
Before running any macro that clears cells, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even if the macro only takes a moment to run.
How to Add and Run the Macro
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) using File > Save As. A regular.xlsxfile cannot store VBA macros. - Open the VBA editor with Alt+F11 on Windows. On some Mac keyboards, use Option+F11 or Fn+Option+F11, depending on the keyboard settings.
- In the editor, choose Insert > Module. Paste the code into the new module, then return to Excel.
- Run the macro by pressing Alt+F8, selecting
ClearEntryContents, and choosing Run. You can also run it from the VBA editor with the cursor inside the procedure and F5.
If the Developer tab is not visible in Excel for Windows, enable it at File > Options > Customize Ribbon and select Developer. In Excel for Mac, open Excel > Settings > Ribbon & Toolbar and enable the Developer tab. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop application for these steps.
Excel may show a security warning when a workbook contains macros. Only enable macros in files you trust. An organization can also restrict macro use through its security policies, so a missing or disabled Run option may be due to those settings rather than a problem with the code.
Choosing the Right Cells to Clear
For a reusable form, fixed addresses are usually the clearest option. Decide which cells are genuinely user inputs, then list those ranges explicitly. In the example, the code clears two blocks and preserves all other sheet contents. If your input area is one rectangle, such as B3:D12, use that address instead—but only if every cell in the rectangle is safe to clear.
Be cautious about including a whole column or worksheet. A command such as ws.Cells.ClearContents removes contents throughout that sheet, including formulas that may be far below the visible form. It can be appropriate for a temporary data sheet designed to be emptied completely, but it is a poor default for a mixed-use worksheet.
Another approach is to clear the currently selected cells. This is flexible, but it depends on what is selected when the macro runs. If a user selects a heading, a formula, or the entire sheet by mistake, the macro will clear that selection. A selection-based macro should therefore be clearly labeled and should prompt the user or otherwise verify its target. For routine resets, a fixed, named input range is generally easier for other workbook users to trust.
You can also use a named range, such as EntryInputs, to make the target more readable than a long list of addresses. Define the name in Excel’s Name Manager and use it as the argument to ClearContents. If the name is later changed to include a formula cell, however, the macro will clear that formula too; named ranges do not automatically make a target safe.
Clear Contents Versus Clear All and Delete
The Excel interface and VBA offer several operations that sound similar but have different outcomes. Choosing the right one prevents formatting loss and unwanted shifts:
- Clear Contents: removes cell values and formulas while retaining cell formatting. The VBA equivalent is
Range.ClearContents. - Clear All: removes contents and a broader set of cell properties, including formatting. In VBA,
Range.Clearis the corresponding broad clear operation. - Delete cells: removes cells and may shift nearby cells up or left, depending on the chosen option. It changes the worksheet layout rather than simply emptying cells.
- Press Delete: on Windows, pressing the Delete key clears contents from the selected cells without deleting the cells. It is a manual action, not a reusable macro.
For example, if B3 has a currency number format and a border that should remain for the next user, ClearContents removes its current number or formula but retains the presentation. Clear All would remove that formatting as well. Neither clear operation should be confused with deleting the worksheet row or column.
Common Problems and How to Avoid Them
- The macro says the worksheet cannot be found: check the spelling of the sheet tab name, including spaces. “Entry Form” and “Entry” are different names.
- The macro clears a formula: inspect every cell in the target range. ClearContents does not distinguish between user input and a formula, so exclude calculation cells from the range.
- The macro stops on a protected sheet: worksheet protection can prevent changes to locked cells. Either have the sheet owner adjust the protection or design the workbook’s protection workflow deliberately. Do not assume that a macro should bypass a password.
- Nothing appears to happen: the confirmation prompt may have been answered No, or the specified cells may already be empty. Check that macros are enabled and that you ran the correct procedure.
- The macro is missing after saving: confirm that the workbook was saved as
.xlsm, not.xlsx. Saving a macro-containing workbook in a format that cannot retain VBA can remove the macro.
Merged cells and unusual worksheet layouts can also make range choices less straightforward. If a target intersects merged areas, test the range on a copy of the workbook and keep the target definition simple. Similarly, if the sheet contains hidden rows or columns, a fixed address can include them even when they are not visible. Review the actual address, not only what appears on screen.
Safety, Confirmation, and Workbook Design
A confirmation box is a helpful guardrail, but it is not a substitute for a correct range. The example asks before clearing, then acts only when the response is Yes. For shared workbooks, make the prompt specific—such as naming the sheet or form—so users understand what will be emptied. A clearly labeled “Reset form” button can also reduce accidental runs, but the macro should still target only intended input cells.
Keep formulas, fixed labels, and instructions outside the input range where practical. If a worksheet has a predictable form layout, documenting the input addresses in a nearby note or in the workbook’s instructions helps future maintainers update the macro safely. Test any changes on a duplicate workbook with representative data, including formulas and formatting that must remain.
Do not rely on Ctrl+Z as a recovery plan after a macro runs. Excel’s normal undo history does not restore VBA edits. The dependable safeguards are a saved backup, a carefully limited target, and a confirmation step. If the data is important, consider whether the workflow should archive a copy before clearing it rather than permanently removing it.
When a Manual Clear or Another Tool Is Better
A macro is useful when the same reset happens regularly, involves multiple separated ranges, or needs a one-click control. For a one-time change, selecting the cells and choosing Home > Clear > Clear Contents is often simpler and avoids enabling VBA. The keyboard Delete key also clears selected contents on Windows; Mac keyboard behavior depends on the keyboard, and the menu command is the reliable cross-platform route.
If the goal is to remove only typed values while retaining formulas in the same range, a plain ClearContents macro is too broad: it clears both. You would need a more selective procedure that identifies constants and leaves formulas alone, and it should be tested carefully before use. If the task is to refresh imported data, a Power Query refresh or a purpose-built data import process may be more appropriate than manually clearing a mixed worksheet. Choose the simplest method that matches the data and the level of repeatability required.
Frequently Asked Questions
Does ClearContents remove formatting?
No. The VBA method Range.ClearContents clears values and formulas but retains cell formatting. Use a broader clear operation only if removing formatting is also intended.
Can I clear cells without deleting formulas?
Not if those formula cells are included in a direct ClearContents target: formulas are cleared along with values. Exclude formula cells from the target or use a deliberately designed procedure that clears only constants.
Can I undo an Excel macro with Ctrl+Z?
No. Excel does not provide its ordinary undo history for changes made by VBA macros. Save a backup before running a clearing macro, and check its target range before confirming.
Will this macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the Excel desktop app for Windows or Mac to run VBA.
Why does the macro fail on a protected worksheet?
Protection may block changes to locked cells. The workbook’s protection settings must allow the intended change, or the sheet owner must adjust the workflow; check the protection design rather than assuming the macro can clear protected cells.
Related
- Can You Password Protect an Excel File? How It Works
- Can You Merge Excel Spreadsheets? Methods That Work
- Can You Merge Two Excel Spreadsheets? Methods Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Enum in VBA: What It Is and How to Use It with ExamplesIn VBA, Enum is a standard language keyword for declaring an enumeration: a named set of related constants,…
- Excel VBA Find Last Row: Reliable Methods and ExamplesIn Excel VBA, find the last row in one column with Cells(Rows.Count, "A").End(xlUp).Row, replacing A with the column…
- Excel Certifications and Macros: What Exams Actually CoverYes—some Excel certifications assess basic macro skills, but they do not all test them, and a macro task…
- Excel VBA Current Date and Time: Use Now, Date, and TimeIn Excel VBA, use Now to get the current date and time, Date for the current date, or…
- Outlook Macros: How to Enable Them Safely in Classic OutlookTo enable VBA macros in classic Outlook for Windows, open File > Options > Trust Center > Trust…
- Excel VBA Date and Time Stamp: Add Fixed TimestampsTo add a fixed date-and-time stamp with Excel VBA, assign Now to a cell and apply a date-time…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.