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.

Excel VBA: Find the Next Empty Cell in Range

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.

Excel VBA: Find the Next Empty Cell in Range

  • 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

Excel VBA: Find the Next Empty Cell in Range

Code Explanation:

In the above code, we used the Range.End property of VBA Excel that allows us to move to the last nonblank 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 nonblank 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.

Excel VBA: Find the Next Empty Cell in Range

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

Excel VBA: Find the Next Empty Cell in Range

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

Excel VBA: Find the Next Empty Cell in Range

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

Similar Readings:


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

Excel VBA: Find the Next Empty Cell in Range

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

Excel VBA: Find the Next Empty Cell in Range

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

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

2 Comments
  1. It does not work at all.

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

  2. Hi Arda
    Hope you are doing well.

    I checked the code you mentioned above and it works. To make it more clear I’m attaching some images with the code.

    Here, I tried the exact code in the same dataset.
    MsgBox Range("E5").End(xlToRight).Offset(0, 1).Address

    You can see the result $G$5.

    Again I changed the dataset slightly.

    Here, the result is also based on the location.

    NB. If it doesn’t help you then please send your dataset to [email protected] or [email protected]

    Thanks
    Shamima Sultana
    ExcelDemy

Leave a reply

ExcelDemy
Logo