Clear Excel VBA: Contents, Formats, and Cells

VBA & macros · Updated November 18, 2026

To clear cells with Excel VBA, use Range("B2:D20").ClearContents to remove values and formulas while keeping formatting, or Range("B2:D20").Clear to remove cell contents and formatting together. Choose the method that matches what you want to preserve, and save a copy of the workbook before running a macro because VBA actions cannot be undone with Ctrl+Z.

What “clear” means in Excel VBA

In everyday Excel use, “clear” can mean several different things: emptying a cell, removing its appearance, or deleting the cells so other cells move into their place. VBA has separate methods for those jobs. Selecting the wrong one can erase formulas, remove carefully applied formatting, or shift a worksheet layout unexpectedly.

The most common choice is ClearContents. It removes a cell’s value or formula but leaves its formatting in place. For example, if B2 contains the formula =A2*1.1, clearing B2 removes the formula and its displayed result. The cell’s number format, fill color, borders, and other direct formatting remain.

Clear is broader: it clears the range rather than only its contents. Use it when you want to reset a block of cells, not when you simply want to empty an input area while retaining its appearance. If the aim is to remove cells and shift nearby cells, use Delete instead; that is a structural change, not just a clearing operation.

Clear contents, formats, or everything

These methods correspond to different outcomes. Decide what should remain before writing the macro:

  • ClearContents removes constants and formulas. It preserves cell formatting, including number formats, fills, fonts, and borders.
  • ClearFormats removes direct formatting, such as a manually applied font, fill, border, or number format. It does not remove the cell’s value or formula.
  • Clear clears the cell range more completely, including its contents and direct formatting. It is the VBA counterpart to a broad “clear all” action.
  • Delete removes cells and can shift other cells up or left. Specify a shift direction when the intended result matters.

Conditional formatting deserves separate attention. It is controlled by rules, rather than being only a cell’s ordinary direct formatting. If a cell still appears colored after a formatting-clearing operation, inspect the conditional-formatting rules. In Excel for Microsoft 365 on Windows, select the cells and use Home > Conditional Formatting > Clear Rules. Depending on the choice, you can clear rules from the selected cells or from the entire sheet.

Clear a fixed range with a macro

For a known input area, specify the worksheet and range explicitly. This avoids clearing a similarly positioned range on whichever sheet happens to be active. The following macro clears entries from B2:D20 on a worksheet named Entry but leaves its formatting intact:

Sub ClearInputCells()
    Dim ws As Worksheet

    ' Save a copy of the workbook before running; macros cannot be undone.
    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Range("B2:D20").ClearContents
End Sub

ThisWorkbook means the workbook containing the macro. That distinction matters if another workbook is open: ActiveWorkbook refers to whichever workbook is currently active, which may not be the one that stores the code. The worksheet name in the example must match the tab name exactly; otherwise VBA raises a subscript-out-of-range error.

To remove both contents and direct formatting from that same area, change the method to Clear. To remove only formatting, use ClearFormats. Keeping a fixed range is often safer than relying on Excel’s used range when the form has a known, stable layout.

Clear entries through the last used row

When a list grows over time, a macro can find the last non-empty row in a dependable column and clear the data rows while leaving the header alone. In this example, column A must be populated for every record; the macro clears columns A through F from row 2 to the last occupied row in A.

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

    ' Save a copy before running; Ctrl+Z cannot reverse a macro.
    Set ws = ThisWorkbook.Worksheets("Entry")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

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

The row check prevents the macro from clearing the header when there are no data rows below it. The result depends on column A being a reliable indicator: if records can have blank cells in A, choose a column that is always filled. Otherwise, the macro may stop at an earlier record and leave later entries behind. A worksheet with inconsistent data may need a different last-row strategy, such as searching across several columns.

Be cautious with UsedRange. It returns Excel’s used range, which can include cells that were formatted or edited in the past even if they now look empty. It may therefore extend farther than the visible data. A deliberate range or a last-row calculation based on a meaningful column is usually easier to reason about.

Clear a table without removing its headers

Excel tables, also called ListObjects in VBA, can expand as rows are added. If the table has a data body, its DataBodyRange covers the data rows but not the header row. Check that the range exists before clearing it: a table with no data rows has no data body to clear.

Sub ClearTableEntries()
    Dim ws As Worksheet
    Dim tbl As ListObject

    ' Save a copy before running; macro changes cannot be undone.
    Set ws = ThisWorkbook.Worksheets("Entry")
    Set tbl = ws.ListObjects("SalesTable")

    If Not tbl.DataBodyRange Is Nothing Then
        tbl.DataBodyRange.ClearContents
    End If
End Sub

This leaves the table’s header row and table structure in place while clearing the body cells. It also keeps direct formatting, so the table remains visually organized. Clearing the body is different from deleting table rows: if the goal is to reduce the table’s row count, use table-row deletion or resize the table deliberately instead.

Clear cells versus delete cells

A cleared cell remains where it is. Other cells do not move to fill the gap. That makes clearing appropriate for reusable forms, templates, and fixed-layout reports. Deleting a cell changes the worksheet structure: Excel may shift nearby cells up or left, and formulas that refer to affected cells can change as a result.

For example, if B4:B8 contains entries in a fixed report, Range("B4:B8").ClearContents empties those locations and keeps the rows in place. If you instead delete those cells and shift cells up, the content originally below row 8 can move into the deleted area. To delete a whole worksheet row intentionally, use the row’s Delete method; do not substitute a clear command and expect rows to close up.

Use clearing when the worksheet layout is meant to stay stable. Use deletion only when removing the cells or rows themselves is part of the requirement and you have checked the impact on adjacent data, formulas, tables, and references.

Run the macro safely

On Windows, open the Visual Basic Editor with Alt+F11, or select Developer > Visual Basic. In the editor, choose the workbook in the Project pane, then use Insert > Module to add a standard module and paste the code there. Run a macro from the editor with the cursor inside its procedure, or return to Excel and use Developer > Macros.

If the Developer tab is missing, enable it in Excel through File > Options > Customize Ribbon. Save a workbook containing VBA in a macro-enabled format, such as .xlsm; saving as a macro-free .xlsx file does not preserve VBA code. Only enable macros for workbooks you trust.

Excel for Mac also supports VBA, but its menus and keyboard behavior differ from Windows. The Visual Basic Editor is available from the Developer tab, and the ribbon can be configured through Excel’s settings. Keyboard shortcuts can depend on the Mac keyboard and function-key settings, so use the ribbon command if a Windows shortcut does not apply. Excel for the web cannot create, run, or edit VBA macros; open the workbook in the desktop Excel app to use this code.

Common problems and checks

If a clearing macro fails or seems to clear the wrong cells, check the workbook and worksheet references first. A misspelled sheet name causes an error, while using ActiveSheet can target a different sheet if the user has changed tabs. Explicit references such as ThisWorkbook.Worksheets("Entry") reduce that risk.

  • Nothing appears to happen: confirm that the specified range contains data and that the macro is being run from the correct workbook.
  • The formatting disappeared: the code may use Clear instead of ClearContents. Choose the narrower method if the format should remain.
  • Some records remain: verify that the column used to find the last row is filled for every record and that the macro’s range includes all relevant columns.
  • A cell still has a color: check conditional formatting rules; a rule can continue to display formatting even after direct formatting is cleared.
  • The macro reports that a sheet or table is missing: check the exact tab name or table name in Excel. A table name is not necessarily the same as the worksheet name.

Clearing cell contents does not mean deleting every object on a worksheet. Shapes, charts, buttons, and other objects are separate from cell values. If a macro is intended to reset a worksheet completely, define whether those objects, validation rules, comments, hyperlinks, and conditional-formatting rules should also be removed instead of assuming a single cell method handles every kind of worksheet content.

Frequently Asked Questions

How do I clear a cell but keep its formatting in VBA?

Use the cell or range’s ClearContents method, such as Range("B2").ClearContents. It removes a value or formula but keeps the cell’s ordinary formatting.

What is the difference between Clear and ClearContents?

ClearContents removes values and formulas while preserving formatting. Clear clears the range more broadly, including its contents and direct formatting. Neither method is the same as deleting cells and shifting neighboring data.

Can I undo a VBA clear with Ctrl+Z?

Generally, no. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy or test the macro on sample data before running it on important work.

How do I clear a worksheet but keep the sheet itself?

Use the worksheet’s cells range, for example Worksheets("Entry").Cells.Clear, if the intention is to clear the cells across that sheet. This does not delete the worksheet, and it does not automatically remove separate objects such as charts or shapes. Confirm that clearing all cell content and formatting is truly intended before using it.

Does VBA clearing work in Excel for the web?

No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run or edit VBA macros. Use desktop Excel for Windows or Mac to run VBA.

Related

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.