VBA Range.Clear: What It Clears, Keeps, and How to Use It

In Excel VBA, Range.Clear clears the specified cells, removing their values or formulas and their cell formatting, along with other cell-level information. Use it only when you want a broad reset; if you need to preserve formatting, use ClearContents instead.

What Range.Clear Does

Clear is a method of Excel’s Range object. It acts on the cells in the range you specify, not on the whole worksheet unless you explicitly pass the whole sheet’s cells. For example, clearing A1:C5 affects that rectangular block. It does not delete the rows or columns themselves, shift neighboring cells, or remove the worksheet.

The method is intentionally broad. It removes the cells’ contents, including typed values and formulas, and clears their ordinary cell formatting, such as number formats, font styling, fills, and borders. It can also clear other cell-level elements, so do not treat it as a contents-only operation. If there is anything in the selected cells that must remain, choose a narrower method and check the result on a copy first.

Range.Clear is VBA code, not a formula to enter in a worksheet cell. A minimal example is Range("A1:C5").Clear, but a macro should generally qualify the range with a worksheet so that the result does not depend on which sheet happens to be active.

How to Write a Safe Clear Macro

This example clears a defined input area on a worksheet named Entry. It uses ClearContents rather than Clear because input forms commonly need their labels, borders, and number formats left intact.

Sub ClearEntryInputs()
    Dim ws As Worksheet

    ' Target the intended sheet explicitly.
    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Range("B2:D20").ClearContents
End Sub

Before running any macro that clears cells, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken range may require closing without saving or restoring a backup. In Excel for Microsoft 365 on Windows, put the code in a standard module in the Visual Basic Editor, then run the macro from the editor or Excel’s macro tools. Excel for the web cannot run or edit VBA macros. Excel for Mac supports VBA, though its editor and some platform-specific VBA features differ; this basic range operation is generally portable.

For a one-time manual clear, select the cells and press Delete on Windows. On Mac, use the Delete key for the equivalent contents-clearing action. That keyboard action is not the same as a VBA Range.Clear: it normally removes cell contents while leaving formatting in place.

Clear, ClearContents, and ClearFormats Compared

These methods are easy to confuse because their names are similar, but they have different purposes. Choose according to what must be preserved:

Clear removes contents and formatting, and is the broad reset option. Use it when the cells should be returned to a largely blank state.
ClearContents removes values and formulas while keeping the cells’ formatting. Use it to empty an input area without changing its appearance.
ClearFormats removes cell formatting while leaving values and formulas in place. Use it to reset visual styling without deleting data.

For example, with a worksheet called Entry, Worksheets("Entry").Range("B2:D20").ClearContents leaves the formatting in B2:D20 alone. By contrast, Worksheets("Entry").Range("B2:D20").Clear also removes that formatting. Both examples require the worksheet reference to be valid in the workbook containing the code. These methods do not delete the cells or shift surrounding data.

There is no need to call ClearContents and then ClearFormats if you intend to clear everything: Clear expresses that broader intention directly. Conversely, using Clear when only values should go is unnecessary and risks removing useful formatting or other cell-level settings.

Choosing the Right Range

The choice of range is as important as the method. A fixed reference such as Range("B2:D20") is easy to audit and is often best for a form with a known layout. A broad reference such as Cells.Clear targets every cell on the worksheet, which can erase far more than intended. A whole-column reference such as Columns("B:D").Clear also reaches cells outside the visible data area.

In a reusable macro, qualify every range. An unqualified expression such as Range("A1").Clear refers to the active sheet, which can change when a user clicks another tab. A reference such as ThisWorkbook.Worksheets("Entry").Range("A1").Clear identifies both the workbook containing the macro and the worksheet. This is especially helpful when a macro opens or activates other workbooks.

If the amount of data changes from run to run, avoid guessing at a large block. Identify the intended last row using a stable column that is populated for each relevant record, then clear only the appropriate cells. A range ending at the last used row in one column may not cover data in another column, so check the table layout and whether formulas or notes lie beyond the input area. If a table has totals, headers, or formulas that must remain, exclude them deliberately.

Clearing Only Populated Input Rows

For a simple list where column A contains a value on every input row, the following macro finds the last populated row in that column and clears the corresponding input columns B through D. It leaves the header row untouched. If column A can contain gaps or is not a reliable indicator of the data boundary, this approach may not capture every relevant row.

Sub ClearCurrentInputRows()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Column A must identify each populated input row.
    Set ws = ThisWorkbook.Worksheets("Entry")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow >= 2 Then
        ws.Range("B2:D" & lastRow).ClearContents
    End If
End Sub

The If condition prevents the macro from attempting to clear a data range when there are no rows below the header. This example clears contents only; replace the method with Clear only if removing the formatting and other cell-level information is also intended. Save a workbook copy before running it, because the macro’s changes cannot be reversed with Ctrl+Z.

What It Does Not Do

Clearing a range is different from deleting cells. Clear leaves the worksheet grid in place, so cells below or beside the range do not move. To remove rows and shift data, use a row-deletion operation instead; that is a structural change and can affect formulas, references, and layout differently.

It is also different from deleting a worksheet. A range-level method affects only its target cells. If formulas elsewhere refer to the cleared cells, those formulas remain, but their results may change because their inputs are now blank. A formula inside the cleared range is removed along with the other contents.

Excel features such as conditional formatting can be governed by rules whose applied ranges extend beyond the cells you are clearing. Do not assume that clearing a few cells removes a rule everywhere it applies. If the goal is to change conditional-formatting rules, inspect them separately through Home > Conditional Formatting > Manage Rules. Similarly, if data validation, hyperlinks, comments, or notes matter, verify the outcome on a copy rather than assuming that a broad clear preserves them.

Common Errors and Misconceptions

Clearing the wrong worksheet: An unqualified Range or Cells reference uses the active sheet. Assign a worksheet variable and qualify the range through it.
Using Clear when only values should go: This can remove formatting that makes a form readable or keeps numbers and dates displayed correctly. Prefer ClearContents for that task.
Assuming blank-looking cells are empty: A cell may contain a formula that displays an empty string. ClearContents removes that formula too, so check the formula bar or the underlying data before clearing.
Expecting Ctrl+Z to restore a macro: Excel’s normal undo history does not provide a dependable undo for VBA changes. Keep a backup before running destructive code.
Using an oversized range for convenience: Clearing entire columns may remove notes, formulas, or other content far below the visible records. Target the smallest range that meets the requirement.

A further practical check is to confirm the workbook and sheet names before execution. A misspelled worksheet name causes a runtime error, while an unintended but valid sheet name can produce a successful macro that changes the wrong place. Explicit references help prevent both kinds of problem.

When to Use Each Method

Use ClearContents for recurring resets of entry fields, staging areas, or imported data where the layout and formatting should survive. Use ClearFormats when the values are correct but unwanted styling needs to be removed. Use Clear when the target area is disposable and should be reset broadly before it is reused.

For a form that contains labels, formulas, input cells, and instructions, avoid clearing one large rectangle unless all of those elements are intended to disappear. Separate input cells from formula cells where possible, then clear only the input range. That design makes the macro easier to understand and reduces the chance of deleting calculations that the form depends on.

If the task is to remove records from an Excel table, consider whether you mean to clear the values in the table’s data rows or delete the rows themselves. Clearing values leaves the table structure and its rows; deleting rows changes the table’s size. The right choice depends on whether the blank rows should remain available for future input.

Frequently Asked Questions

Does VBA Range.Clear delete formulas?
Yes. A formula is cell content, so Range.Clear removes it along with constants such as text and numbers. Use ClearFormats if you want to keep formulas and values while removing formatting.

Does Range.Clear remove formatting?
Yes. Removing cell formatting is one of the key differences between Clear and ClearContents. If the cells should keep their number formats, fills, borders, or font styling, use ClearContents.

What is the difference between Clear and ClearContents?
Clear is the broader reset and removes contents as well as formatting and other cell-level information. ClearContents removes values and formulas but preserves formatting, making it the usual choice for emptying an input range.

Can I undo a VBA Range.Clear?
Do not rely on Ctrl+Z to undo a macro. VBA operations generally clear Excel’s normal undo history, so save a separate copy of the workbook before running code that removes data or formatting.

Does Range.Clear delete rows or cells?
No. It clears the specified cells but does not remove rows or shift neighboring cells. Use a row or cell deletion operation if you need the worksheet structure to change.

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.