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.

**Table of Contents**hide

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

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 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:** FindNext Using VBA in ExcelÂ

**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
```

**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

**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.

**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.

**Conclusion**

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

It does not work at all.

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

Hi

ArdaHope 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 SultanaExcelDemy

thank you.thank to this script my work is made easier

Dear

Dr,You are most welcome.

Regards

ExcelDemy