• Login
  • Courses
  • Books
  • Cheat Sheets
  • Tutorials Archive
  • VBA Code Generator
  • Contact

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Check if Cell is Empty: Step-by-Step Guide and 4 Examples to Check if Cell Range is Empty with Macros

Excel VBA Tutorial about how to check if cell or range is empty with macros

In this VBA Tutorial, you learn how to check if a cell or range is empty.

This VBA Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Table of Contents

  • Related VBA and Macro Tutorials
  • #1: Check if Cell is Empty
    • VBA Code to Check if Cell is Empty
    • Process Followed by VBA Code to Check if Cell is Empty
    • VBA Statement Explanation
    • Macro Example to Check if Cell is Empty
    • Effects of Executing Macro Example to Check if Cell is Empty
  • #2: Check if Active Cell is Empty
    • VBA Code to Check if Active Cell is Empty
    • Process Followed by VBA Code to Check if Active Cell is Empty
    • VBA Statement Explanation
    • Macro Example to Check if Active Cell is Empty
    • Effects of Executing Macro Example to Check if Active Cell is Empty
  • #3: Check if Range is Empty
    • VBA Code to Check if Range is Empty
    • Process Followed by VBA Code to Check if Range is Empty
    • VBA Statement Explanation
    • Macro Example to Check if Range is Empty
    • Effects of Executing Macro Example to Check if Range is Empty
  • #4: Check if Any Cell in Range is Empty
    • VBA Code to Check if Any Cell in Range is Empty
    • Process Followed by VBA Code to Check if Any Cell in Range is Empty
    • VBA Statement Explanation
    • Macro Example to Check if Any Cell in Range is Empty
    • Effects of Executing Macro Example to Check if Any Cell in Range is Empty
  • References to VBA Constructs Used in this VBA Tutorial

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

  • Learn about commonly-used VBA terms here.

  • Learn about the Excel Object Model and how to refer to objects here.

  • Learn how to create references to cell ranges here.

  • Learn how to declare and work with variables here.

  • Learn about data types here.

  • Learn how to work with worksheet functions within VBA here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Check if Cell is Empty

VBA Code to Check if Cell is Empty

To check if a cell is empty with VBA, use a macro with the following statement structure:

If IsEmpty(Cell) Then
    StatementsIfCellIsEmpty
Else
    StatementsIfCellIsNotEmpty
End If

Process Followed by VBA Code to Check if Cell is Empty

Check if Cell is empty > Execute StatementsIfCellIsEmpty or StatementsIfCellIsNotEmpty

VBA Statement Explanation

Line #1: If IsEmpty(Cell) Then

  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (IsEmpty(Cell)).

      • If the condition is met and returns True: StatementsIfCellIsEmpty are executed.

      • If the condition isn't met and returns False: StatementsIfCellIsNotEmpty are executed.

  2. Item: IsEmpty(…).

    • VBA Construct: IsEmpty function.

    • Description: Generally, the IsEmpty function indicates whether a variable has been initialized. Nonetheless, you can also use IsEmpty to check if a cell is empty.

      The IsEmpty function:

      • Takes one parameter (expression) of the Variant data type. Within this macro structure, the parameter is a Range object (Cell).

      • Returns True if the variable is uninitialized or explicitly set to Empty. Otherwise, IsEmpty returns False.

  3. Item: Cell.

    • VBA Construct: Range object.

    • Description: Range object representing the cell you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with the Range.Item) or Range.Offset properties. If you explicitly declare an object variable to represent Cell, use the Range object data type.

  4. Item: IsEmpty(Cell).

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: This condition is an expression that evaluates to True or False. The IsEmpty function (IsEmpty(Cell)) returns True or False, as follows:

      • True: Cell is empty.

      • False: Cell is not empty.

Line #2: StatementsIfCellIsEmpty

  1. Item: StatementsIfCellIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(Cell)) returns True. Within this macro structure, IsEmpty(Cell) returns True if Cell is empty.

Line #3: Else

  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfCellIsNotEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(Cell)) returns False. Within this macro structure, IsEmpty(Cell) returns False if Cell is not empty.

Line #4: StatementsIfCellIsNotEmpty

  1. Item: StatementsIfCellIsNotEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(Cell)) returns False. Within this macro structure, IsEmpty(Cell) returns False if Cell is not empty.

Line #5: End If

  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.

Macro Example to Check if Cell is Empty

The following macro example checks if cell A5 of the worksheet named “Check if Cell is Empty” (myCell) is empty and displays a message box confirming whether the cell is empty or not empty.

Sub checkIfCellIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'declare object variable to hold reference to cell you work with
    Dim myCell As Range

    'identify cell you work with
    Set myCell = ThisWorkbook.Worksheets("Check if Cell is Empty").Range("A5")

    'check if cell is empty. Depending on result, display message box indicating whether cell is empty (True) or not empty (False)
    If IsEmpty(myCell) Then
        MsgBox myCell.Address & " is empty"
    Else
        MsgBox myCell.Address & " is not empty"
    End If

End Sub

Effects of Executing Macro Example to Check if Cell is Empty

The following GIF illustrates the results of executing the macro example. Cell A5 (This cell isn't empty) is not empty and the message box displayed confirms that this is the case.

Macro checks if cell is empty

#2: Check if Active Cell is Empty

VBA Code to Check if Active Cell is Empty

To check if the active cell is empty with VBA, use a macro with the following statement structure:

If IsEmpty(ActiveCell) Then
    StatementsIfActiveCellIsEmpty
Else
    StatementsIfActiveCellIsNotEmpty
End If

Process Followed by VBA Code to Check if Active Cell is Empty

Check if active cell is empty > Execute StatementsIfActiveCellIsEmpty or StatementsIfActiveCellIsNotEmpty

VBA Statement Explanation

Line #1: If IsEmpty(ActiveCell) Then

  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (IsEmpty(ActiveCell)).

      • If the condition is met and returns True: StatementsIfActiveCellIsEmpty are executed.

      • If the condition isn't met and returns False: StatementsIfActiveCellIsNotEmpty are executed.

  2. Item: IsEmpty(…).

    • VBA Construct: IsEmpty function.

    • Description: Generally, the IsEmpty function indicates whether a variable has been initialized. Nonetheless, you can also use IsEmpty to check if a cell is empty.

      The IsEmpty function:

      • Takes one parameter (expression) of the Variant data type. Within this macro structure, the parameter is a Range object (ActiveCell).

      • Returns True if the variable is uninitialized or explicitly set to Empty. Otherwise, IsEmpty returns False.

  3. Item: ActiveCell.

    • VBA Construct: Application.ActiveCell property.

    • Description: The Application.ActiveCell property returns a Range object representing the active cell.

  4. Item: IsEmpty(ActiveCell).

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: This condition is an expression that evaluates to True or False. The IsEmpty function (IsEmpty(ActiveCell)) returns True or False, as follows:

      • True: Active cell is empty.

      • False: Active cell is not empty.

Line #2: StatementsIfActiveCellIsEmpty

  1. Item: StatementsIfActiveCellIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(ActiveCell)) returns True. Within this macro structure, IsEmpty(ActiveCell) returns True if the active cell is empty.

Line #3: Else

  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfActiveCellIsNotEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(ActiveCell)) returns False. Within this macro structure, IsEmpty(ActiveCell) returns False if the active cell is not empty.

Line #4: StatementsIfActiveCellIsNotEmpty

  1. Item: StatementsIfActiveCellIsNotEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (IsEmpty(ActiveCell)) returns False. Within this macro structure, IsEmpty(ActiveCell) returns False if the active cell is not empty.

Line #5: End If

  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.

Macro Example to Check if Active Cell is Empty

The following macro example checks if the active cell is empty and displays a message box confirming whether the active cell is empty or not empty.

Sub checkIfActiveCellIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'check if active cell is empty. Depending on result, display message box indicating whether active cell is empty (True) or not empty (False)
    If IsEmpty(ActiveCell) Then
        MsgBox "The active cell is empty"
    Else
        MsgBox "The active cell is not empty"
    End If

End Sub

Effects of Executing Macro Example to Check if Active Cell is Empty

The following GIF illustrates the results of executing the macro example. The active cell (A6) is empty and the message box displayed confirms that this is the case.

Macro checks if active cell is empty

#3: Check if Range is Empty

VBA Code to Check if Range is Empty

To check if a range is empty with VBA, use a macro with the following statement structure:

If WorksheetFunction.CountA(CellRange) = 0 Then
    StatementsIfRangeIsEmpty
Else
    StatementsIfRangeIsNotEmpty
End If

Process Followed by VBA Code to Check if Range is Empty

Check if number of non-empty cells in range is 0 > Execute StatementsIfRangeIsEmpty or StatementsIfRangeIsNotEmpty

VBA Statement Explanation

Line #1: If WorksheetFunction.CountA(CellRange) = 0 Then

  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (WorksheetFunction.CountA(CellRange) = 0).

      • If the condition is met and returns True: StatementsIfRangeIsEmpty are executed.

      • If the condition isn't met and returns False: StatementsIfRangeIsNotEmpty are executed.

  2. Item: WorksheetFunction.CountA(…).

    • VBA Construct: WorksheetFunction.CountA method.

    • Description: The WorksheetFunction.CountA method counts the number of cells that are not empty within the argument list (CellRange). For these purposes, a cell is deemed to not be empty if, for example, it contains an error value or empty text (“”).

  3. Item: CellRange.

    • VBA Construct: Range object.

    • Description: Range object representing the cell range you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range property. If you explicitly declare an object variable to represent CellRange, use the Range object data type.

  4. Item: =.

    • VBA Construct: = comparison operator.

    • Description: The = comparison operator compares the 2 expressions to determine whether they're equal:

      • The expression to the left of the = comparison operator (WorksheetFunction.CountA(CellRange)).

      • The expression to the right of the = comparison operator (0).

  5. Item: WorksheetFunction.CountA(CellRange) = 0.

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: The condition is an expression that evaluates to True or False. The = comparison operator returns True or False as follows:

      • True: If WorksheetFunction.CountA(CellRange) returns 0. This occurs when CellRange is empty.

      • False: If WorksheetFunction.CountA(CellRange) returns a value other than 0. This occurs when CellRange isn't empty.

Line #2: StatementsIfRangeIsEmpty

  1. Item: StatementsIfRangeIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) = 0) returns True. Within this macro structure, (WorksheetFunction.CountA(CellRange) = 0) returns True if CellRange is empty.

Line #3: Else

  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfRangeIsNotEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) = 0) returns False. Within this macro structure, (WorksheetFunction.CountA(CellRange) = 0) returns False if CellRange is not empty.

Line #4: StatementsIfRangeIsNotEmpty

  1. Item: StatementsIfRangeIsNotEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) = 0) returns False. Within this macro structure, (WorksheetFunction.CountA(CellRange) = 0) returns False if CellRange is not empty.

Line #5: End If

  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.

Macro Example to Check if Range is Empty

The following macro example checks if the range composed of cells A7 through A11 of the worksheet named “Check if Cell is Empty” (myCellRange) is empty and displays a message box confirming whether the range is empty or not empty.

Sub checkIfRangeIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'declare object variable to hold reference to cell range you work with
    Dim myCellRange As Range

    'identify cell range you work with
    Set myCellRange = ThisWorkbook.Worksheets("Check if Cell is Empty").Range("A7:A11")

    'check if number of non-empty cells in range is 0. Depending on result, display message box indicating whether cell range is empty (True) or not empty (False)
    If WorksheetFunction.CountA(myCellRange) = 0 Then
        MsgBox myCellRange.Address & " is empty"
    Else
        MsgBox myCellRange.Address & " is not empty"
    End If

End Sub

Effects of Executing Macro Example to Check if Range is Empty

The following GIF illustrates the results of executing the macro example. Cells A7 through A11 (with fill) are empty and the message box displayed confirms that this is the case.

Macro checks if range is empty

#4: Check if Any Cell in Range is Empty

VBA Code to Check if Any Cell in Range is Empty

To check if any cell in a range is empty with VBA, use a macro with the following statement structure:

If WorksheetFunction.CountA(CellRange) < CellRange.Count Then
    StatementsIfAnyCellInRangeIsEmpty
Else
    StatementsIfNoCellInRangeIsEmpty
End If

Process Followed by VBA Code to Check if Any Cell in Range is Empty

Check if number of non-empty cells in range is less than total number of cells in range > Execute StatementsIfAnyCellInRangeIsEmpty or StatementsIfNoCellInRangeIsEmpty

VBA Statement Explanation

Line #1: If WorksheetFunction.CountA(CellRange) < CellRange.Count Then

  1. Item: If… Then.

    • VBA Construct: Opening statement of If… Then… Else statement.

    • Description: The If… Then… Else statement conditionally executes a group of statements depending on the value of an expression. For these purposes:

      • The If… Then… Else statement tests the specified condition (WorksheetFunction.CountA(CellRange) < CellRange.Count).

      • If the condition is met and returns True: StatementsIfAnyCellInRangeIsEmpty are executed.

      • If the condition isn't met and returns False: StatementsIfNoCellInRangeIsEmpty are executed.

  2. Item: WorksheetFunction.CountA(…).

    • VBA Construct: WorksheetFunction.CountA method.

    • Description: The WorksheetFunction.CountA method counts the number of cells that are not empty within the argument list (CellRange). For these purposes, a cell is deemed to not be empty if, for example, it contains an error value or empty text (“”).

  3. Item: CellRange.

    • VBA Construct: Range object.

    • Description: Range object representing the cell range you work with.

      You can usually return a Range object with constructs such as the Worksheet.Range property. If you explicitly declare an object variable to represent CellRange, use the Range object data type.

  4. Item: <.

    • VBA Construct: < comparison operator.

    • Description: The < comparison operator compares 2 expressions to determine whether (i) the expression to its left (WorksheetFunction.CountA(CellRange)), (ii) is less than (iii) the expression to its right (CellRange.Count).

  5. Item: CellRange.Count.

    • VBA Construct: Range.Count property.

    • Description: The Range.Count property returns the number of objects in the collection (CellRange). Within this macro structure, the Count property returns the number of individual cells within CellRange.

  6. Item: WorksheetFunction.CountA(CellRange) < CellRange.Count.

    • VBA Construct: Condition of If… Then… Else statement.

    • Description: The condition is an expression that evaluates to True or False. The < comparison operator returns True or False as follows:

      • True: If WorksheetFunction.CountA(CellRange) returns a value smaller than the value returned by CellRange.Count. Within this macro structure, this occurs when (i) the number of non-empty cells in CellRange (returned by WorksheetFunction.CountA(CellRange)) (ii) is less than (iii) the number of cells in CellRange (returned by CellRange.Count). This occurs when CellRange contains at least 1 empty cell.

      • False: If WorksheetFunction.CountA(CellRange) returns a value equal to the value returned by CellRange.Count. Within this macro structure, this occurs when (i) the number of non-empty cells in CellRange (returned by WorksheetFunction.CountA(CellRange)) (ii) is equal to (iii) the number of cells in CellRange (returned by CellRange.Count). This occurs when CellRange contains no empty cells.

Line #2: StatementsIfAnyCellInRangeIsEmpty

  1. Item: StatementsIfAnyCellInRangeIsEmpty.

    • VBA Construct: Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) < CellRange.Count) returns True. Within this macro structure, (WorksheetFunction.CountA(CellRange) < CellRange.Count) returns True if CellRange contains at least 1 empty cell.

Line #3: Else

  1. Item: Else.

    • VBA Construct: Else clause of If… Then… Else statement.

    • Description: The statements below the Else clause (StatementsIfNoCellInRangeIsEmpty) are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) < CellRange.Count) returns False. Within this macro structure, (WorksheetFunction.CountA(CellRange) < CellRange.Count) returns False if CellRange doesn't contain any empty cells.

Line #4: StatementsIfNoCellInRangeIsEmpty

  1. Item: StatementsIfNoCellInRangeIsEmpty.

    • VBA Construct: Else Statements within If… Then… Else statement.

    • Description: One or more VBA statements that are executed if the condition tested in the opening statement of the If… Then… Else statement (WorksheetFunction.CountA(CellRange) < CellRange.Count) returns False. Within this macro structure, (WorksheetFunction.CountA(CellRange) < CellRange.Count) returns False if CellRange doesn't contain any empty cells.

Line #5: End If

  1. Item: End If.

    • VBA Construct: Closing statement of If… Then… Else statement.

    • Description: The End If clause marks the end of the If… Then… Else block.

Macro Example to Check if Any Cell in Range is Empty

The following macro example checks if the range composed of cells A13 through A17 of the worksheet named “Check if Cell is Empty” (myCellRange) contains any empty cells and displays a message box confirming whether the range contains or not any empty cells.

Sub checkIfAnyCellInRangeIsEmpty()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-cell-empty/

    'declare object variable to hold reference to cell range you work with
    Dim myCellRange As Range

    'identify cell range you work with
    Set myCellRange = ThisWorkbook.Worksheets("Check if Cell is Empty").Range("A13:A17")

    'check if number of non-empty cells in range is less than total number of cells in range. Depending on result, display message box indicating whether cell range contains any empty cell (True) or not (False)
    If WorksheetFunction.CountA(myCellRange) < myCellRange.Count Then
        MsgBox myCellRange.Address & " contains at least 1 empty cell"
    Else
        MsgBox myCellRange.Address & " doesn't contain empty cells"
    End If

End Sub

Effects of Executing Macro Example to Check if Any Cell in Range is Empty

The following GIF illustrates the results of executing the macro example. Cell A15 is empty. The message box displayed confirms that the cell range containing cells A13 to A17 (with fill) contains at least one empty cell (A15).

Macro checks if any cell in range is empty

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage within the Microsoft Developer Network:

  1. Identify the cell or cell range you work with:

    • Workbook object.

    • Application.ThisWorkbook property.

    • Worksheet object.

    • Workbook.Worksheets property.

    • Range object.

    • Worksheet.Range property.

    • Worksheet.Cells property.

    • Range.Item property.

    • Range.Offset property.

    • Application.ActiveCell property.

  2. Test if a cell or cell range is empty:

    • If… Then… Else statement.

    • IsEmpty function.

    • WorksheetFunction.CountA method.

    • Range.Count property.

    • Comparison operators.

  3. Display a message box including, among others, the address of a cell or cell range:

    • MsgBox function.

    • Range.Address property.

    • & operator.

  4. Work with variables and data types:

    • Dim statement.

    • Set statement.

    • = operator.

    • Data types:

      • Boolean data type.

      • String data type.

      • Variant data type.
guest
guest
4 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure

Copyright © 2015–2023 PDS Intelligence Pte. Ltd. All rights reserved.
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the Microsoft Corporation.