To clear cell contents with an Excel macro, use VBA’s ClearContents method on the cells you want to empty; it removes values and formulas without removing cell formatting. For example, Range("B2:D10").ClearContents clears that range on the active worksheet, while a macro can target a particular sheet or the current selection.
What clearing cell contents does
In Excel, clearing contents means removing what cells contain, not removing the cells themselves. A cell’s entered number, text, date, or formula is cleared. The cells stay in place, and their formatting—such as fill color, borders, number formats, and fonts—remains. Existing conditional formatting also remains.
This can be useful when reusing an entry form or template. For example, clearing an order form’s input cells can leave its headings, borders, and currency formats ready for the next order. But if the range includes formulas you need, those formulas are removed too. A macro does not distinguish between a manually entered value and a formula unless you write extra code to select only one type.
Clearing is different from deleting cells. The Delete command in Excel’s ribbon can remove cells and shift other cells up or left; ClearContents leaves the grid structure and surrounding cell positions unchanged. It is also different from Clear, which removes more than contents, including formatting. Choose the operation based on what you need to preserve.
Clear contents without a macro
If you only need to empty cells occasionally, a macro may be unnecessary. Select the cells and press Delete on Windows. On a Mac, use the Delete key (which commonly works as Backspace on Mac keyboards). This clears the selected cells’ contents without changing their formatting. You can also use the ribbon: Home > Clear > Clear Contents.
For a repeatable task, the ribbon command is a useful way to confirm the intended result before automating it. If you need the same set of cells cleared often, a macro can perform the operation consistently, especially when the range is fixed or the action needs to be available through a button.
Make a macro for the selected cells
This macro clears whichever cell range is selected when you run it. It first checks that the current selection is a range, rather than a chart or another object.
Sub ClearSelectedContents()
Dim target As Range
' Stop if the current selection is not a cell range.
If TypeName(Selection) <> "Range" Then Exit Sub
Set target = Selection
target.ClearContents
End Sub
To add it in Excel for Microsoft 365 on Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, and choose Insert > Module. Paste the code into the module. Return to Excel, select the cells, and run the macro from Developer > Macros. If the Developer tab is not visible, turn it on in File > Options > Customize Ribbon by selecting its checkbox.
The macro works on one selected range or a multi-area selection. Because it acts on the current selection, check the highlighted cells before running it. If the selection contains a whole table or worksheet, the macro will clear that selection’s contents too. This is convenient for an interactive cleanup, but it is less predictable than a macro that names a specific range.
Clear a fixed range on a named worksheet
For a form or repeatable process, explicitly name the worksheet and range. This example asks for confirmation before clearing cells B3:D20 on a worksheet named Entry.
Sub ClearEntryCells()
Dim ws As Worksheet
Dim answer As VbMsgBoxResult
' This workbook must contain a worksheet named Entry.
Set ws = ThisWorkbook.Worksheets("Entry")
answer = MsgBox("Clear contents in B3:D20 on Entry?", _
vbYesNo + vbQuestion, "Confirm clear")
If answer <> vbYes Then Exit Sub
ws.Range("B3:D20").ClearContents
End Sub
Replace Entry and B3:D20 with the actual worksheet tab name and cell range. ThisWorkbook means the workbook that contains this VBA code; it is not necessarily the workbook currently active on screen. That distinction helps avoid clearing cells in the wrong workbook when several files are open. If the named sheet does not exist, VBA will stop with an error, so verify the tab name, including spaces.
The confirmation prompt is a practical safeguard, not a replacement for checking the range. If the macro is used frequently and the range is always correct, you can remove the prompt. For higher-risk workbooks, consider adding a separate backup or logging step rather than relying on a confirmation alone.
Choose the right range and preserve formulas
The most important decision is which cells the macro should affect. A fixed address is clear and repeatable, but it may need updating if the layout changes. A selection-based macro adapts to the cells a user chooses, but it depends on careful selection. A table or named range can make a workbook easier to maintain when its data area grows or moves.
Before clearing a mixed range, decide whether formulas should be kept. The examples above remove formulas as well as typed values. One simple design is to keep formulas outside the input area and clear only designated input cells. If that is not possible, write code that identifies the intended cells before clearing them; do not assume a broad range contains only data entry.
- Fixed range: Use a named worksheet and an explicit address for a stable form, such as B3:D20.
- Current selection: Use a selection-based macro when the person running it should choose the cells each time.
- Named range: Use a workbook or worksheet name for an input area that may move; update the name in Excel’s Name Manager when the layout changes.
- Whole row or column: Be especially cautious. A broad selection can remove contents far beyond the visible portion of a worksheet.
Clearing a formula cell can change results elsewhere. For instance, a summary formula that refers to the cleared cell may calculate differently afterward, depending on the formula and its other inputs. If the workbook feeds reports or calculations, inspect dependent results after running the macro.
ClearContents compared with Clear and Delete
Use ClearContents when you want to keep formatting and cell positions but remove values or formulas. Use Excel’s Home > Clear > Clear Formats when formatting is the problem but the cell values should remain. The broader Home > Clear > Clear All command removes contents and formatting, along with other cell attributes that Clear All covers.
Deleting is appropriate when cells themselves should be removed and neighboring cells should move to fill the gap. That is a structural change, not just an emptying operation, and it can affect formulas or layout. For a reusable form, clearing contents is usually the more targeted choice. If you also need to remove data validation, comments, or other cell features, check those separately rather than assuming ClearContents removes them.
Run macros safely and save the workbook correctly
Save a copy of the workbook before running a macro that clears data. Excel macros cannot be undone with Ctrl+Z, so the usual Undo history is not a reliable recovery method. A saved copy gives you a way to restore the earlier contents if the range was wrong.
To keep VBA in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). The standard .xlsx format does not store VBA code, so saving a macro-enabled file as .xlsx can remove the macro. Only enable macros in files from sources you trust. Do not change Excel’s security settings to allow every macro merely to run a particular workbook; use the organization’s approved process or trust the specific file as appropriate.
VBA macros run in the desktop Excel application. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Excel for Mac supports VBA, though the interface and keyboard shortcuts differ; use the Visual Basic Editor and Macro commands available in the Mac desktop app. The code shown here uses standard range methods and does not depend on a Windows-only worksheet feature.
Common problems and how to avoid them
- The macro clears the wrong cells: A selection-based macro acts on the current selection. Confirm the highlighted range first, or switch to an explicit worksheet and address.
- The macro says a subscript is out of range: The worksheet name in the code does not match a sheet in the workbook containing the code. Check spelling and spaces.
- Nothing happens or Excel blocks the macro: Confirm the file is saved as .xlsm and that macros are permitted for this trusted file under your organization’s security rules.
- The code stops on a protected worksheet: A sheet’s protection can prevent changes to locked cells. Use the workbook’s authorized protection process; do not assume the macro should bypass protection.
- A merged-cell range causes trouble: Avoid selecting only part of a merged area. Select the complete merged cell area or adjust the layout before running the macro.
- The result cannot be reversed with Undo: This is expected for VBA actions. Restore from a saved copy or another approved backup.
Frequently Asked Questions
Does ClearContents remove formatting?
No. It removes cell values and formulas while leaving formatting in place. Use a formatting-specific clear command if you want to remove formatting instead.
Will this macro clear formulas too?
Yes. ClearContents removes both formulas and typed values in the target range. Keep formulas outside the cleared area or add code that deliberately limits which cells are cleared.
Can I clear cells in every worksheet at once?
Yes, VBA can loop through worksheets, but that is risky because it may erase data in sheets you did not intend to change. For a workbook-wide macro, specify the target sheets and ranges explicitly, then save a copy before running it.
Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the file in the desktop Excel app to run the code.
Can I assign the macro to a button?
Yes. In desktop Excel, add a button from Developer > Insert under Form Controls, then assign the macro. A button makes the task easier to repeat, but label it clearly and keep a confirmation prompt if users could select or clear important data.
Related
- LibreOffice: How to Merge Cells in Calc and Format Them
- Split Cell Data Into Columns in Excel: Best Methods
- Power Query Merge: How to Join Tables in Microsoft Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find a Cell Containing a Specific Date ValueTo find a cell containing a specific date with Excel VBA, loop through the date column and compare…
- VBA Select Multiple Ranges to Copy and PasteIn Excel VBA, combine nonadjacent ranges with Application.Union when you need to select them, but copy each range…
- Excel Macro to Protect a Sheet With a PasswordTo protect the active worksheet with a password in Excel for Microsoft 365 on Windows, run a VBA…
- Excel VBA IsError: Check for Worksheet Errors in CodeIn Excel VBA, IsError(expression) returns True when an expression evaluates to an error value and False otherwise. Use…
- Sort Using VBA in Excel: Code Examples, Options, and TipsYes. In Excel for Microsoft 365 on Windows, you can sort data with VBA by defining the full…
- VBA Error Handler: How On Error Works and When to Use ItIn VBA, an error handler is code that responds when a statement fails while a procedure is running.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.