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

This article illustrates how to find the next empty cell in a range using VBA code in Excel with 4 different examples. We’ll use Excel’s built-in functions in our code to find an empty cell. While working with a large dataset that is imported from various sources, finding out empty cells is an important task before performing calculations. Let’s dive into the examples to learn how to find the empty cells of your dataset.

Download Practice Workbook

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

4 Examples to Find the Next Empty Cell in Range Using VBA in Excel

Here 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:

Follow the steps to open the visual basic editor and write some code there.

• 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 some code there and press F5 to run.

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

In this example, we’re going to find the next empty cell in a row using VBA code in Excel. In the following screenshot, we can see that the dataset has an empty cell in row no 5.

Let’s 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:

In the above code, 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)

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

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

In this example, 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 also 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–

In this example, we set Offset(0,1) to achieve our goal.

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

We can easily show the address of the empty cell by adding the following line in the code.

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

Read More: How to Ignore Blank Cells in Range in Excel (8 Ways)

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

Similarly, we can search for the next empty cell in a column by changing the direction property in the Range.End method. Let’s 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

Read More: VBA to Count Blank Cells in Range in Excel (3 Methods)

Similar Readings:

• How to Apply Conditional Formatting in Excel If Another Cell Is Blank
• Remove Blanks from List Using Formula in Excel (4 Methods)
• How to Set Cell to Blank in Formula in Excel (6 Ways)
• Null vs Blank in Excel
• How to Delete Empty Cells in Excel (6 Methods)

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

In this example, we’ll find 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

In the above code, 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. Here the function is applied to each of the cells in the specified range to find the empty cell.

Read More: How to Find Blank Cells Using VBA in Excel (6 Methods)

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

Using the Application.ActiveCell property, we can get the next empty cell in a row or column. We can 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).

Now 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 which is the next empty cell.

Read More: How to Skip Blank Rows Using Formula in Excel (8 Methods)

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.

Conclusion

Now, we know how to find the next empty cell in range using VBA code in Excel with 4 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
• How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
• If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
• How to Fill Blank Cells with 0 in Excel (3 Methods)
• Remove Blank Cells Using Formula in Excel (7 Methods)