Excel VBA: Find the Next Empty Cell in Range: 4 Examples

We’ve used a dataset that contains a sale list of a super shop. The dataset has a bunch of empty cells and we want to find them before performing any calculations.

Write Code in Visual Basic Editor:

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module.

Now that a new module is opened, write the code and press F5 to run.

Method 1 – Use of the VBA Code to Find the Next Empty Cell in a Row Range in Excel

Find the next empty cell in a row using VBA in Excel; the dataset has an empty cell in row no 5.

Apply the VBA code to find and select the empty cell in row 5.

Sub FindNextEmptyCell()
Range("B5").End(xlToRight).Offset(0, 1).Select
End Sub

Code Explanation:

We used the Range.End property of VBA Excel that allows us to move to the last non–blank cell of a row or column. The syntax of the Range.End property is-

expression.End(direction)

The expression is the cell address of the cell from where we want to start searching for the empty cell.  

The direction argument has four options xlDown, xlTop, xlToLeft, and xlToRight to move to any of the four directions from the starting range.

We used B5 as the expression as we’re looking to find an empty cell in row 5 and it is the first cell of row 5. And we set xlToRight as the direction so that the code searches for the empty cell to the right side of cell B5.

We used the VBA Offset function to locate the empty cell. As the Range.End property finds out the last non–blank cell of the specified row, we need to set 1 as the ColumnOffset argument to get the next cell i.e., the blank cell. The VBA Offset function takes two arguments–

We set Offset(0,1) to achieve our goal.

If we put E5 as the starting range, we’ll get the next empty cell G5.

Show the address of the empty cell by adding the following line in the code.

MsgBox Range("E5").End(xlToRight).Offset(0, 1).Address

Method 2 – Run a VBA Code to Find the Next Empty Cell in a Column Range in Excel

Search for the next empty cell in a column by changing the direction property in the Range.End method. Run the code in the visual basic editor to find the next empty cell of the specified starting range in column B of our dataset.

Sub FindNextEmptyCell()
Range("B2").End(xlDown).Offset(1).Select
MsgBox Range("B2").End(xlDown).Offset(1).Address
End Sub

Method 3 – Find the Next Empty Cell in a Range Using VBA in Excel

The next empty cell in a specified range. It’ll search each of the cells in the range for an empty cell. The following code finds the next empty cell in the range B7:I9.

Sub FindNextEmptyCell()
    On Error Resume Next
    Dim emptyCell As Range
    For Each emptyCell In ActiveSheet.Range("B7:I9").Cells
        If Len(emptyCell) = 0 Then
            emptyCell.Select
            Exit For
        End If
    Next
End Sub

We used the VBA Len function to find the empty cell. The Len function returns the number of characters of a string that is supplied as an argument. The function is applied to each of the cells in the specified range to find the empty cell.

Method 4 – Find the Next Empty Cell of the Active Cell in a Row or Column

Using the Application.ActiveCell property, the next empty cell in a row or column. Use this instead of specifying the starting range as we did in Example 1 and Example 2. For example, let’s select a cell in column B (B4 in this example).

Copy and paste the following code in the visual basic editor and press F5 to run it.

Sub FindNextEmptyCell()
Application.ActiveCell.End(xlDown).Offset(1).Select
End Sub

The above code selected cell B9; the next empty cell.

Notes

• While writing multiple codes it is necessary to keep the right sequence.
• In example 1 we used the Address property to get the cell reference of the empty cell.
• The MsgBox function is used to show the cell reference of the empty cell.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• How to Find Exact Match Using VBA in Excel
• Find Last Row with Data in a Range Using Excel VBA Macros
• Excel VBA to Find Value in Column
• Excel VBA to Find Matching Value in Column
• How to Find Last Row Using Excel VBA
• Excel VBA to Find Multiple Values in Range