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

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

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