How to Clear Input Cells with an Excel VBA Macro

The finished VBA macro

For a simple form, the safest approach is to name the exact input cells and clear their contents. That is much safer than clearing a whole row, column, or worksheet.

Public Sub ClearInputCells()
    Const SHEET_NAME As String = "Entry"
    Dim ws As Worksheet

    On Error GoTo CleanFail
    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ws.Range("B4,B6,B8,D4:D8").ClearContents

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

CleanFail:
    MsgBox "The input cells could not be cleared: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

The key line is ws.Range("B4,B6,B8,D4:D8").ClearContents. The comma-separated reference lets one statement target several non-adjacent inputs plus one continuous range.

Why ClearContents is the right command

ClearContents removes the value or formula stored in a cell, but it leaves most of the cell setup alone. In an input form, that is usually exactly what you want: the typed entries disappear, while fill colors, borders, number formats, and data-validation drop-downs remain available for the next entry.

Avoid Cells.Clear for a reset button. That command can wipe the entire worksheet, including formatting. Also avoid broad ranges such as Range("B:D").ClearContents unless every cell in those columns is truly disposable. A reset macro should be precise.

Choose the cells you actually want to reset

Before changing the code, separate the worksheet into two groups: cells the user is allowed to type into, and cells Excel calculates or displays automatically. Only the first group belongs in the reset range.

• Single input cells: use references such as B4,B6,B8.
• Continuous input blocks: use a range such as D4:D8.
• Several blocks: combine them in the same Range string, separated by commas.
• Formula cells: leave them out completely.

If your form changes often, a named range such as InputCells can make maintenance easier. The macro can then use ws.Range("InputCells").ClearContents, and you can change the named range later without editing VBA.

Add the macro to your workbook

1. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
2. Open the Visual Basic Editor.
3. Insert a standard module.
4. Paste the macro into that module.
5. Change Entry to your real worksheet name if needed.
6. Replace B4,B6,B8,D4:D8 with your actual input cells.
7. Run the macro once from the editor before assigning it to a button.

Using ThisWorkbook matters. It makes the code target the workbook that contains the macro rather than whichever workbook happens to be active on screen. The same workbook-qualified pattern appears in the AutoFit used-range VBA guide.

Why events are temporarily disabled

If the input cells have Worksheet_Change logic attached to them, clearing several cells can trigger that event repeatedly. Application.EnableEvents = False prevents those event procedures from firing during the reset.

The macro restores events in both the normal exit and the error path. That detail is important: if a macro stops while events are disabled, other workbook automation may appear broken until Excel is restarted or events are turned back on manually. For another event-driven example, see the timestamp VBA tutorial.

Common mistakes to avoid

Mistake	What happens
Cells.Clear	Can erase the entire sheet, including formatting.
Range("B:D").ClearContents	May remove formulas or labels that happen to sit in those columns.
Using an unqualified Range	The wrong active sheet can be changed.
Including formula cells in the reset range	Calculated results disappear instead of recalculating.
Disabling events without a safe exit	Other workbook event macros can stop responding.
Testing directly on the only copy	A wrong range reference can destroy data before you notice it.

Test the reset before adding a button

Enter temporary values into every intended input cell, note the formulas and totals around them, and then run the macro. The test is successful only if the typed values are removed and the calculated areas remain unchanged or recalculate normally.

• Confirm every intended input is blank.
• Confirm formulas are still present.
• Check that drop-down lists still work.
• Check number formats and cell colors.
• Run the macro a second time while the input cells are already blank.

Frequently asked questions

Can I clear non-adjacent cells?

Yes. Put the addresses in one Range reference separated by commas, as shown in the example.

Will data validation stay in the cells?

Normally yes. ClearContents removes the current entry without removing the validation rule.

Can I use a named range instead?

Yes. A named range is a good option when the input area changes regularly or when you want the VBA to be easier to read.

Will this work on Excel for Mac?

The VBA used here relies on standard Excel object-model commands and does not require Windows APIs or ActiveX controls, so the macro itself is suitable for desktop Excel on both Windows and Mac.

Can I assign the macro to a shape or button?

Yes. After the macro works correctly, assign ClearInputCells to a worksheet shape or supported form button so users can reset the form without opening the VBA editor.