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

Get FREE Advanced Excel Exercises with Solutions!

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