VBA Range Is Empty? How to Check Cells Correctly

To check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make sure the Range variable itself is not Nothing: an unassigned Range object and a range of blank cells are two different situations.

Check a range with CountA

CountA is usually the clearest test when “empty” means that every cell in a range has no content. It counts cells containing values, text, errors, or formulas. If its result is zero, no cell in the range contains any of those things. Formatting alone does not make a cell count as filled.

For example, if A1:C10 is the area to inspect, pass that Range object to Application.CountA. The method works for a single cell, a rectangular block, or a range made up of multiple areas. It is useful for deciding whether to skip a calculation, display a message, or avoid processing an unused input section.

Here is a procedure that checks an incoming range. A caller can pass a normal range or an unassigned object reference; the two conditions are handled separately.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even if a particular example only displays a message.

Public Sub ReportRangeStatus(ByVal target As Range)
    ' Check the object reference before asking Excel about its cells.
    If target Is Nothing Then
        MsgBox "No range was supplied."
        Exit Sub
    End If

    If Application.CountA(target) = 0 Then
        MsgBox "The range contains no values or formulas."
    Else
        MsgBox "The range contains at least one value or formula."
    End If
End Sub

The test reports whether the range has content; it does not change the cells. It also does not decide whether a value is meaningful for your task. A cell containing zero, for instance, is not empty according to this test, even if a later business rule treats zero as “no result.”

What “empty” means in Excel

People use “empty” to describe several different cell states. A cell can be genuinely blank, contain a formula that displays nothing, contain spaces, or have formatting but no content. These cases may look alike on the worksheet, but VBA and Excel functions can treat them differently. Choosing the test starts with deciding which state matters.

Genuinely blank: no value or formula has been entered in the cell.
Formula returning an empty string: a formula is present, but its displayed result may look blank.
Whitespace: the cell contains a space or another character that is difficult to see.
Formatted blank: the cell has a fill, border, number format, or other formatting but no entered content.
Unassigned object: a VBA Range variable has not been set to refer to any worksheet cells.

For most “is there anything in this input area?” checks, CountA gives the expected answer: formulas count, even when their displayed result is an empty string. For questions about what a person can see, you may need a different test. For example, a formula that returns an empty string can be visually blank without being an empty cell.

Why IsEmpty is not a general range test

IsEmpty checks whether a Variant variable has the special Empty state. That is useful when checking a Variant that has never been assigned a value, but it is not a dependable all-cells-are-blank test for a multi-cell range. Reading the value of a multi-cell range generally gives VBA an array of cell values, and that array itself is not the special Empty value—even if all the cells represented are blank.

For a single cell, IsEmpty(cell.Value) can identify an unentered cell because reading that cell can return an Empty Variant. But it will not treat a formula returning "" as genuinely empty: the formula result is a zero-length string, not the Empty Variant. A test that works on one cell should not automatically be applied to a block of cells.

Use CountA for a whole-range content check. Reserve IsEmpty for checking an individual Variant or, with care, a single cell’s value when the distinction between Empty and a string matters. Also note that Range.Value2 returns a scalar for one cell and a two-dimensional array for a multi-cell range, which is another reason a single-cell test may behave differently when expanded.

Formulas, spaces, and cells that look blank

A common surprise is that CountA reports a range as populated even though every visible cell appears blank. One likely cause is a formula such as an IF formula that returns an empty string when there is no input. Excel displays an empty-looking result, but the formula still occupies the cell, so CountA counts it.

Spaces are another cause. A cell containing one ordinary space is not empty, and CountA counts it. A formula that returns spaces is also content. This often happens when data has been pasted from another source or a formula deliberately adds padding. Formatting, by contrast, does not count: a blank cell with a colored fill is still blank to CountA.

If the question is whether a range has any displayed text or value after ignoring ordinary leading and trailing spaces, inspect each cell’s value. The following example treats errors as content, ignores formula results that are empty strings, and ignores ordinary spaces at the beginning or end of a value. It does not remove every possible whitespace character, such as a nonbreaking space.

Public Sub CheckVisibleContent(ByVal target As Range)
    Dim cell As Range
    Dim hasVisibleContent As Boolean

    ' A missing object is different from a blank worksheet area.
    If target Is Nothing Then Exit Sub

    For Each cell In target.Cells
        If IsError(cell.Value2) Then
            hasVisibleContent = True
            Exit For
        ElseIf Len(Trim$(CStr(cell.Value2))) > 0 Then
            hasVisibleContent = True
            Exit For
        End If
    Next cell

    If hasVisibleContent Then
        MsgBox "At least one cell has visible content."
    Else
        MsgBox "No visible content was found."
    End If
End Sub

This is a different rule from “contains no formulas.” If formulas returning empty strings should count as occupied, use CountA instead. If you need to detect formulas specifically, inspect individual cells with cell.HasFormula in a loop; do not infer formula presence from what appears on screen.

Nothing is not the same as an empty range

A Range variable is an object reference. Before it is assigned with Set, it can be Nothing. In that state it does not refer to a worksheet cell at all. By contrast, a range such as A1:C10 is a valid Range object even when every cell in it is blank. Check for Nothing before calling properties or methods on a variable that might not have been assigned.

Keep the checks separate. VBA’s Or operator does not short-circuit, so an expression that combines “is this Nothing?” with a call such as CountA(target) may still evaluate the CountA call when the object is Nothing. That can raise an error. An If block followed by a separate content check, as in the example above, is safer and easier to understand.

A normal Excel Range refers to one or more worksheet cells; it is not a zero-cell container. If code produces an invalid address, Excel may raise an error while creating the range rather than return a valid but empty Range object. For example, do not use a fabricated row-zero address as a way to represent “no cells.” Use Nothing to represent no object, or use a valid range and test its contents.

Choose the test that matches the question

Different checks answer different questions. Decide whether you care about formulas, displayed results, or the object reference before choosing a method:

Does the range contain any values or formulas? Use Application.CountA(target) = 0 to test for none.
Is this one cell an unentered cell? Check its value with IsEmpty, keeping in mind that a formula returning an empty string is not Empty.
Does a VBA variable refer to a range? Use target Is Nothing before accessing the range.
Does anything appear after trimming ordinary spaces? Loop through the cells and test their values, accounting for errors and the whitespace rules your task needs.
Should a formula returning an empty string count as blank? Decide this explicitly. CountA counts the formula; a displayed-content check can ignore its empty-string result.

Excel’s worksheet function COUNTBLANK answers a related but different question: it counts blank cells and also cells whose formulas return empty text. Therefore, checking whether the blank count equals the number of cells can classify formula-empty results as blank, unlike CountA. Be precise about the intended meaning, especially when formulas are part of the range.

Common problems and practical safeguards

If a check unexpectedly says “not empty,” inspect the cells rather than immediately changing the test. Look for formulas, spaces, error values, or imported characters. Selecting a cell and examining the formula bar can reveal content that is not apparent in the grid. In VBA, the Immediate window can also help during debugging, but remember that a multi-cell Value2 result is an array rather than one simple value.

Large ranges can take longer to inspect cell by cell. For a straightforward whole-range check, CountA is generally simpler and avoids writing a VBA loop. Use a loop when you need a special rule, such as ignoring formulas that return empty text or trimming spaces. If only a specific input block matters, test that block rather than an entire worksheet column; a stray value far below the visible data can make a whole-column check appear populated.

Be careful when using the result to decide whether to delete or overwrite cells. “Empty” can mean no content, no visible result, or no meaningful business data, and those definitions are not interchangeable. Test representative cases—including zero, an error, a formula returning an empty string, and a space—before connecting the check to code that changes workbook data.

Frequently Asked Questions

How do I check whether a VBA range is completely blank?
Confirm the Range variable is not Nothing, then test whether Application.CountA(target) equals zero. This treats formulas, errors, numbers, text, and spaces as content.

Why does CountA say a blank-looking range is not empty?
The cells may contain formulas that return empty strings, spaces, or other hard-to-see characters. Formulas count even when their displayed result looks blank; ordinary formatting alone does not.

Can I use IsEmpty on a range?
It can be useful for a single cell’s value, but it is not a reliable test for every cell in a multi-cell range. Use CountA for the usual whole-range check, or loop through cells when you need a custom definition.

How do I check whether a Range variable is Nothing?
Use If target Is Nothing Then before calling methods such as CountA or accessing cell properties. Keep this check separate from the content check because VBA does not short-circuit Or.

Does CountA count a formula that returns an empty string?
Yes. The cell contains a formula, so it is counted even though the displayed result is empty-looking. If you want to treat that result as blank, inspect cell values using a rule designed for displayed content.

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.