VBA Clear in Excel — ClearContents vs Clear vs Delete, and the Empty-String Trap

TL;DR — There is no single "clear a cell." .ClearContents wipes the value and formula but keeps the formatting — the one you want ninety percent of the time. .Clear wipes everything, formats and borders included, so use it only to reset a cell to factory-blank. .ClearFormats wipes formatting and keeps the values. Setting cell.Value = "" is not blank — it leaves a zero-length string that ISBLANK reads as non-empty. And .Clear is not .Delete: Clear empties the cell in place, Delete removes it and shifts its neighbours.

Range("A2:Z1000").ClearContents   ' data gone, template (borders/colors/formats) kept
Range("A2:Z1000").Clear           ' EVERYTHING gone — you now rebuild the formatting
Range("A2:Z1000").ClearFormats    ' formatting gone, values kept

Range("A2").Value = ""            ' NOT blank — an empty string; ISBLANK is False
Range("A2").ClearContents         ' truly blank

Everyone learns .ClearContents first and then wonders why .Clear "deleted my borders," why a "blank" cell still trips a COUNTA, or why clearing a range broke formulas three columns over. All of it comes from one idea worth holding: a cell is a stack of independent layers, and clearing is choosing which layers to wipe. Pick the eraser that matches the layer you mean, and the surprises disappear.

What you'll learn

The mental model — a cell is layers, and clearing picks which layers to wipe
.ClearContents versus .Clear versus .ClearFormats
Why cell.Value = "" is a fake blank, and what truly empties a cell
Why .Clear and .Delete are different operations, not synonyms
Clearing a whole sheet or used range in one fast call

The mental model: a cell is layers, clearing picks which to wipe

A cell is not one thing. It holds a value or formula, a number format, a font and fill, borders, conditional formatting, data validation, and comments — independent layers stacked in the same cell. There is no method called "clear"; there is a family of methods, each of which removes a chosen subset:

Range("A1").ClearContents   ' removes: value/formula        keeps: everything else
Range("A1").ClearFormats    ' removes: number format, font, fill, borders   keeps: value
Range("A1").Clear           ' removes: ALL of the above

Once you see the layers, the method names read as exactly what they do. The question is never "how do I clear this cell" — it is "which layers do I want gone," and the answer picks the method.

ClearContents vs Clear vs ClearFormats

.ClearContents is the workhorse: wipe the data, keep the template. It removes values and formulas and leaves borders, colors, number formats, and validation intact — which is exactly what "clear last month's numbers so I can enter this month's" means:

Range("B2:B50").ClearContents   ' numbers gone, the formatted table still looks like a table

.Clear is the sledgehammer: it removes the contents and every formatting layer — borders, fills, conditional formatting, data validation, comments. This is the number-one accidental damage in VBA: reaching for .Clear to "empty" a formatted range and discovering the borders and colors you built are gone too.

Range("B2:B50").Clear           ' now B2:B50 is blank AND unformatted — rebuild time

Use .Clear only when you genuinely mean "reset these cells to a brand-new, unformatted state." .ClearFormats is the opposite surgical tool — strip formatting, keep the values — and it has targeted siblings for a single layer: .ClearComments, .ClearHyperlinks, .ClearNotes, .ClearOutline. Pick the layer, pick the method.

The empty-string trap: an empty string is not blank

This is the silent one. Assigning an empty string does not empty a cell — it stores a zero-length string, which is a value, not an absence of one:

Range("A1").Value = ""          ' A1 now holds a zero-length STRING
' ISBLANK(A1) is False · COUNTA counts A1 · IsEmpty(Range("A1")) is False
Range("A1").ClearContents       ' A1 is now genuinely empty
' ISBLANK(A1) is True · COUNTA ignores A1 · IsEmpty(Range("A1")) is True

A cell set to "" looks blank on screen but behaves as filled: COUNTA counts it, ISBLANK and IsEmpty report it as non-empty, Range.End(xlUp) stops at it, and charts and pivot tables treat it as data. This produces maddening "the cell is empty but Excel disagrees" bugs. To empty a cell, use .ClearContents (or assign Empty: Range("A1").Value = Empty), never = "". The distinction between a truly empty cell and an empty string is one of the most useful things to internalise; see VBA Cell Value for how values read back.

Clear is not Delete: the shift that breaks addresses

.Clear and .Delete sound like synonyms and are not. .Clear empties the cell but leaves it where it is. .Delete removes the cell entirely and shifts its neighbours up or left to fill the hole — so every address below or right of the deletion changes, and formulas pointing at the moved cells can break or return #REF!.

Range("A5").ClearContents   ' A5 is empty; A6, A7, ... stay put
Range("A5").Delete Shift:=xlUp   ' A5 is GONE; A6 becomes A5, A7 becomes A6, addresses shift

If your goal is "make this cell empty," it is always .ClearContents — never .Delete. Reach for .Delete only when you actually want the cell, row, or column gone and the shift is the point, such as removing blank rows. Doing row deletion safely (looping bottom-up, using EntireRow) is its own topic; see VBA Delete Rows. Confusing the two is how a "cleanup" macro silently scrambles a sheet.

Clearing a whole sheet or used range in one call

Clearing is a range operation, so do it in one call, not a cell-by-cell loop. Looping crosses from VBA into Excel on every cell and crawls on big ranges; one call on the whole range is a single operation (the same round-trip principle as reading values — see VBA Cell Value):

ws.UsedRange.ClearContents               ' clear all the data on the sheet, keep formats
ws.Cells.Clear                           ' wipe the entire sheet, contents and formats
Range("A2", Range("A2").End(xlDown)).ClearContents  ' clear a column of data below a header

UsedRange and CurrentRegion target "everything with data" without hardcoding a range height; see VBA UsedRange. My rule of thumb: default to .ClearContents. .Clear is a reset-to-factory, not an eraser; .Delete is structural surgery, not an eraser; = "" is a fake-blank bug. Nine times out of ten the verb you want is .ClearContents, and the tenth is .ClearFormats.

How ExcelMaster helps

The clearing mistakes that cost real time are quiet ones: .Clear that took the template's borders with it, the = "" "blank" that a downstream COUNTA kept counting, the .Delete that shifted a column and broke formulas two sheets away. Each does something — just not the something you meant.

ExcelMaster reaches for the eraser that matches your intent. Ask it to "clear the data but keep the layout" and it uses .ClearContents on the used range; ask it to "reset this sheet completely" and it uses .Clear. It empties cells with .ClearContents rather than = "", so your blanks are genuinely blank, and it keeps .Delete for when you actually want rows removed — done bottom-up so nothing shifts underneath it. You say what should be gone; it picks the method that leaves the rest of the cell intact.

Frequently asked questions

What is the difference between Clear and ClearContents in VBA?

.ClearContents removes only the values and formulas, keeping formatting, borders, number formats, and validation. .Clear removes everything — contents and all formatting layers. Use .ClearContents to wipe data while keeping a template intact, and .Clear only when you want the cells reset to a completely blank, unformatted state.

How do I clear cell contents but keep the formatting in VBA?

Use .ClearContents: Range("B2:B50").ClearContents removes the values and formulas while leaving borders, colors, number formats, and data validation in place. Avoid .Clear, which also strips all of that formatting, and avoid = "", which leaves a zero-length string rather than a truly empty cell.

Is setting a cell to an empty string the same as clearing it in VBA?

No. Range("A1").Value = "" stores a zero-length string, which is a value: ISBLANK returns False, COUNTA counts the cell, and IsEmpty reports it as non-empty. To make a cell genuinely blank, use Range("A1").ClearContents or Range("A1").Value = Empty. The empty-string cell looks blank but behaves as filled.

What is the difference between Clear and Delete in VBA?

.Clear (and .ClearContents) empties a cell but leaves it in place, so surrounding addresses do not change. .Delete removes the cell entirely and shifts neighbouring cells up or left to fill the gap, which changes every address below or right of it and can break formulas with #REF!. Use .ClearContents to empty a cell and .Delete only when you want it gone.

How do I clear an entire sheet or used range in VBA?

To clear all data but keep formatting, use ws.UsedRange.ClearContents. To wipe the whole sheet including formats, use ws.Cells.Clear. Both act in a single call, which is far faster than looping cell by cell, and UsedRange targets exactly the region that contains data without hardcoding a range size.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-01.

Related guides: VBA Cell Value · VBA Delete Rows · VBA Copy Paste · VBA UsedRange · VBA Number Format