To clear chosen cells in Excel with a macro, use VBA’s ClearContents method on a worksheet range. For example, Worksheets("Input").Range("B2,D5,F10:F12").ClearContents removes the values and formulas in those cells while leaving their formatting in place.
What a macro clears—and what it leaves behind
A macro is a saved set of VBA instructions that Excel can run on demand. For clearing cells, the key choice is what you want removed. ClearContents deletes cell values and formulas, but keeps cell formatting, such as colors, borders, number formats, and font styles. It is usually the right choice when you want to empty input fields without changing the worksheet’s design.
For instance, if B2 contains a typed customer name, D5 contains a date, and F10:F12 contain formulas, the example clears all five cells. The formulas are removed too; the cells do not retain formulas that will recalculate when other data changes. Check the target range carefully if it contains formulas you need to keep.
Other actions have different effects:
ClearContentsremoves values and formulas but keeps formatting.Clearremoves cell contents and formatting, so it is broader than an ordinary reset.Deleteremoves cells and may shift surrounding cells, depending on the chosen delete option. It is not the normal way to empty a range.
A basic macro for specific cells
This example clears two individual cells and a three-cell range on a worksheet named Input. The worksheet name must match the tab name exactly. In the Visual Basic Editor, insert a standard module and paste in this code:
Sub ClearInputCells()
Dim ws As Worksheet
' Use the worksheet in the workbook that stores this macro.
Set ws = ThisWorkbook.Worksheets("Input")
' Clear only the listed cells and range.
ws.Range("B2,D5,F10:F12").ClearContents
End Sub
The comma-separated addresses in the range refer to nonadjacent areas as well as a continuous range. You can replace them with your own addresses, such as A1,C3,E5:E8. Keep the worksheet qualification: ws.Range(...) makes the target sheet explicit, rather than relying on whichever sheet happens to be active when the macro runs.
ThisWorkbook means the workbook containing the VBA code. If the macro is stored in a separate workbook, such as the Personal Macro Workbook, this reference points to that file, not necessarily the workbook you mean to edit. In that situation, deliberately identify the target workbook and confirm it before running the procedure.
How to add and run the macro in Excel
- Save a copy of the workbook before running any macro. VBA actions cannot be reversed with Ctrl+Z.
- Save the working file as an Excel Macro-Enabled Workbook (
.xlsm) if the code needs to be stored in that workbook. - Open the VBA editor. On Windows, press Alt+F11. On Mac, use Developer > Visual Basic; the keyboard shortcut can vary with the keyboard and function-key settings.
- In the editor, choose Insert > Module, paste the macro into the module, and replace the sample sheet name and cell addresses.
- Return to Excel. On Windows, press Alt+F8, select
ClearInputCells, and choose Run. You can also run it from the VBA editor.
If the Developer tab is missing in Windows Excel, turn it on through File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar and enable Developer. Excel for the web does not create or run VBA macros; open the file in the desktop Excel app to use this code.
Choosing the right range
Use a fixed range when the form layout is stable and you always want to empty the same cells. A list such as B2,D5,F10:F12 is easy to read and audit. It is also less likely to erase unrelated entries than clearing an entire column or worksheet. Before expanding a range, check for formulas, labels, or helper values located among the cells.
If the target cells form one continuous block, use a normal range such as B2:F8. That expression includes every cell in the rectangle, not just cells that currently contain data. If only a few cells in that rectangle should be cleared, list those addresses individually instead.
For a worksheet with named input fields, a named range can make the macro easier to maintain. Create or select a range in Excel and assign it a name through the Name Box or Formulas > Define Name. Then refer to that name in VBA, making sure it is defined in the correct workbook or worksheet scope. A clear name such as EntryFields communicates intent better than a long address list, but review the name’s definition if the worksheet layout changes.
Clear cells only when a condition is met
Sometimes the goal is not to clear a fixed list every time, but to clear a cell only when a rule applies. For example, a reset macro might empty a note field only if a nearby status cell says “Complete.” A condition should be explicit so that a typo or unexpected status does not cause the macro to erase information accidentally.
Sub ClearNoteWhenComplete()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Input")
' Clear C2 only when B2 contains the expected status.
If ws.Range("B2").Value = "Complete" Then
ws.Range("C2").ClearContents
End If
End Sub
In this example, B2 is the status cell and C2 is the note cell. If B2 contains anything other than the exact text “Complete,” the macro leaves C2 alone. For more complex conditions, test the rule on a copy of the workbook and decide how blanks, extra spaces, or different capitalization should be treated before relying on it.
Buttons, repeated use, and maintenance
If people will use the reset repeatedly, you can attach the macro to a button. On Windows desktop Excel, show the Developer tab, choose Developer > Insert, and select a Button under Form Controls. Draw the button on the worksheet, then choose the macro when Excel asks which procedure to assign. Edit the button text to say what will be cleared, such as “Clear entry fields.” Avoid a vague label like “Reset” if the action could remove important input.
Excel for Mac also supports VBA, but the ribbon layout and button setup can differ between releases. Use the Developer tab’s controls and consult the command labels in the installed version rather than assuming every Windows dialog is identical. A button does not add protection: it runs the assigned macro against its specified target range, so the code still needs review.
When the worksheet changes, revisit the addresses or named range and verify that they still identify only disposable input. It can help to keep the macro name descriptive and add a short comment explaining the purpose of each cleared area. This makes later edits safer, especially when someone other than the original author maintains the workbook.
Common problems and how to fix them
- “Subscript out of range”: The workbook or worksheet reference is wrong. Check the exact sheet tab name and whether the macro is using
ThisWorkbookor another workbook reference. - The wrong sheet is affected: Avoid unqualified references such as
Range("B2"). Set a worksheet variable and usews.Range(...)so the target is clear in the code. - The macro does not appear in the Run list: Confirm the procedure is a public, parameter-free
Subin a standard module, and that the file is saved in a macro-enabled format. - The sheet is protected: Excel may prevent changes to locked cells. Review the protection setup and use an authorized way to unprotect the sheet; do not assume the macro should bypass its protections.
- A merged cell causes an error: Merged areas need special care because the visible cell may represent a larger merged range. Inspect the merge and target the intended merged area appropriately rather than expanding the clearing range blindly.
- The cleared cells look unchanged: Their fill, borders, or number format may remain because
ClearContentspreserves formatting. Confirm whether the contents are gone by selecting the cell and checking the formula bar.
Safety and alternatives
Macros can make workbook changes immediately, and clearing data is not undone by Ctrl+Z. Save a separate copy first, test on sample entries, and read the addresses in the code before running it on valuable data. If a workbook came from someone else, inspect what its macros do and follow your organization’s security rules. Do not enable macros just to make an unfamiliar file work.
To save VBA in the workbook, use the .xlsm file type. A standard .xlsx workbook cannot store VBA project code; saving a macro-enabled file as .xlsx can remove its macros. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA. Open the file in desktop Excel when the task depends on this macro.
If you only need to clear cells once, selecting them and using Home > Clear > Clear Contents is often simpler than creating VBA. If you need a repeatable reset, a short macro can reduce manual steps, provided its target range is precise and the user understands what it removes.
Frequently Asked Questions
Does ClearContents remove cell formatting?
No. It removes values and formulas while leaving cell formatting in place. Use Clear only when you also want to remove formatting and other cell contents covered by that method.
Can I clear non-adjacent cells in one macro?
Yes. Put the addresses in one range string separated by commas, such as ws.Range("A2,C4,E6:E8").ClearContents. Check that each listed address belongs to the intended worksheet.
Can I undo a macro with Ctrl+Z?
Generally, no. Running VBA clears Excel’s ordinary undo history for that action. Save a backup copy before running a clearing macro, especially if the workbook contains information that cannot be easily re-entered.
Will this macro run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; the code above targets the worksheet named in the macro.
Why does the macro say the worksheet cannot be found?
Check that the sheet tab name matches the text inside Worksheets("Input"), including spaces. Also check which workbook contains the macro: ThisWorkbook refers to the workbook that stores the code.
Related
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code for UserForm: Build an Excel Data Entry FormTo create a VBA UserForm in Excel, add a UserForm in the Visual Basic Editor, place controls on…
- VBA Dictionary Length: How to Count ItemsIn VBA, use a Dictionary’s Count property to find how many key-value pairs it contains: for example, Debug.Print…
- VBA Range AutoFilter: Examples, Criteria, and FixesIn Excel VBA, use Range.AutoFilter to filter a range that includes its header row; set Field to the…
- VBA MsgBox If: Check a User’s Choice with an If StatementIn VBA, use MsgBox inside an If statement to show a prompt and run different code according to…
- Excel UserForm Date Picker: Options and SetupYes, you can add a date picker to an Excel UserForm, but Excel for Microsoft 365 on Windows…
- VBA Dictionary Get Value by Key: Syntax and ExamplesTo get a value from a VBA Scripting.Dictionary, use its key as the item index: value = dict.Item(key)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.