We’ll use the following sample dataset to check how to go the last non-empty cell.

### Method 1 – Using the XMATCH Function

**The XMATCH function** locates a specified item within an array or cell range and then returns the item’s position within the array or range.

**Steps:**

- Select the
**E5**cell and use the following formula:

`=XMATCH("*",B:B,2,-1)`

- Hit
**Enter**.

- You’ll get the row number of the last non-empty cell of the column.

The first argument of the function,** “*”**, denotes what to look for in the column. The asterisk sign means it will look for cells with values. The next argument,** B:B**, indicates the range of cells the function will look for values. The next argument,** 2**, denotes a **Wildcard character match**. Finally, the **-1** argument means it will match from bottom to top.

### Method 2 – Applying the XLOOKUP Function

**Steps:**

- Select the
**E5**cell and insert the following formula:

`=XLOOKUP("*",B:B,B:B,"",2,-1)`

- Press
**Enter**.

.

- The function will return the content of the last non-empty cell in the column.

The first argument is a **Wildcard character**. The next argument is the **look_array** **B:B**. The third argument is the **return_array**. It is also **B:B**, as we’re returning the value from the same array. The next argument is a blank character. If the function doesn’t find a match, it will return the blank character. The following argument,** 2**, stands for **Wildcard character match**. The final **-1** indicates that the order will match from bottom to top.

**Read More: **How to Select Column to End of Data in Excel

### Method 3 – Using Keyboard Shortcuts

**Steps:**

- Select the first cell of the column.
- Hit
**Ctrl + Down Arrow**.

- We will reach the last non-empty cell.

**Note:**

- This method is applicable only when there are no empty rows inside the dataset.

**Read More:** [Solved!] CTRL+END Shortcut Key Goes Too Far in Excel

### Method 4 – Combining SUMPRODUCT, MAX, and ROW Functions

**Steps:**

- Select the
**E5**cell and use the formula below,

`=SUMPRODUCT(MAX(($B:$B<>"")*(ROW(B:B))))`

- Hit
**Enter**.

- We will get the row number of the last non-empty cell.

**Formula Breakdown:**

**ROW(B:B): The ROW function**returns the row number of all the cells in the**B**column.**MAX(($B:$B<>””)*(ROW(B:B)))**:**The MAX function**returns the maximum value in an array. Here, the**“$B:$B<>”””**is a condition. This condition validates if the cells in the range are nonempty or empty and create an array of**TRUE**and**FALSE**. If empty,it assigns**FALSE**; if not, it assigns**TRUE**. Then, this array gets multiplied with the array returned by**the ROW function**. Finally,**the MAX function**returns the row of the cell that has the maximum value in the array of**the ROW function**. In this case, it is**10**.**SUMPRODUCT(MAX(($B:$B<>””)*(ROW(B:B)))):**Finally,**the SUMPRODUCT**returns the product of**the MAX**array with**the ROW**array which is**10**.

### Method 5 – Using a Combination of INDEX, MAX, and ROW Functions

**Steps:**

- Use the following formula in cell
**E5**:

`=INDEX(B:B,INDEX(MAX(($B:$B<>"")*(ROW(B:B))),0))`

- Hit
**Enter**.

- You’ll get the value of the last non-empty cell in the column.

**Formula Breakdown:**

**ROW(B:B):****The ROW function**returns the row number of all the cells in the**B**column.**MAX(($B:$B<>””)*(ROW(B:B))): The MAX function**returns the maximum value in an array. Here, the**“$B:$B<>”””**is a condition. This condition validates if the cells in the range are nonempty or empty and create an array of**TRUE**and**FALSE**. If empty,it assigns**FALSE**; if not, it assigns**TRUE**. Then, this array gets multiplied with the array returned by**the ROW function**. Finally,**the MAX function**returns the row of the cell that has the maximum value in the array of**the ROW function**. In this case, it is**10**.**INDEX(B:B,INDEX(MAX(($B:$B<>””)*(ROW(B:B))),0)):**Finally,**the INDEX function**in the parenthesis returns the product of**the MAX**array and**the ROW**array, which is**10**. This means the value**10**refers to the**10th**cell in the reference range**B:B**of**the INDEX function**outside the parenthesis. So**the INDEX function**returns the value in the**10th**cell, which is**“Tom”**.

### Method 6 – Applying OFFSET and COUNTA Functions

**Steps:**

- Select the
**E5**cell and use the following formula:

`=OFFSET(B4,COUNTA(B4:B10)-1,0)`

- Hit
**Enter**.

- You will get the value of the last non-empty cell in the
**E5**cell.

**Formula Breakdown:**

**COUNTA(B4:B10):****The COUNTA function**counts the number of non-empty cells in a given range. In this case, the number of nonempty cells in the**B4:B10**range is**7**.**OFFSET(B4,COUNTA(B4:B10)-1,0):****The OFFSET function**takes the**B4**cell as a reference. The function’s next two arguments-**rows**and**cols**– respectively, define how many rows and columns to move from the reference cell. Here, the**rows**argument is**COUNTA(B4:B10)-1**or**7-1**or**6**. The**cols**argument is That means**the OFFSET function**will return the value of the cell that is**6**cells below the**B4**cell. In this case, the value is**“Tom”**.

### Method 7 – Combining INDEX and COUNTA Functions

**Steps:**

- Choose cell
**E5**and enter the following formula:

`=INDEX(B4:B10,COUNTA(B4:B10),0)`

- Hit
**Enter**.

- We will obtain the value of the final non-empty cell in the
**E5**cell.

** Formula Breakdown:**

**COUNTA(B4:B10):****The COUNTA function**counts the number of non-empty cells in a given range. In this case, the number of nonempty cells in the**B4:B10**range is**7**.**INDEX(B4:B10,COUNTA(B4:B10),0): The INDEX function**numbers the cells of the range that is given as a reference. Here,**the INDEX function**will number the cells in the range**B4:B10**from**1**to**7**. It will return the value of the cell in the number**7**The value is**“Tom”**.

### Method 8 – Using the LOOKUP Function

**Steps:**

- Select the
**E5**cell and insert the following formula:

`=LOOKUP(2,1/(B:B<>""),B:B)`

- Hit
**Enter**.

- You will get the value of the last non-empty cell.

Here, **t****he**** LOOKUP function** searches through the **B:B** range as indicated by the third argument. It searches if the cells are empty or not through the expression “ **B:B<>”” **”.When it assigns **TRUE** if the cell is non-empty and **FALSE **otherwise. It returns the result as an array of **TRUE** and **FALSE**. Then, it turns the **TRUE** and **FALSE** into **1** and **-1** respectively. Finally, in the **“1/(B:B<>””)”** expression, the **1’s** and **-1’s** in the array divide the** 1 **value and return an array of **1’s** and **-1’s**.Then, **the INDEX function** looks for **2** in the **B:B** range. Since the column contains only text data, it can’t find it. So, it returns the largest value in the array that is equal to or less than 2. In this case, it’s the last value in the array, which is ** “Tom”**.

### Method 9 – Applying VBA Code

**Steps:**

- Select the dataset.
- Go to the
**Developer**tab in the ribbon. - Select the
**Visual Basic**tab. - The
**Visual Basic**window will be opened.

- Click on
**Insert**. - Select
**Module**. - A coding module will appear.

- Insert the following code and save it.

```
Sub Last_Non_Empty_Cell()
'declaring variable
Dim Non_Empty_Cell As Long
Non_Empty_Cell = Range("B" & Rows.Count).End(xlUp).Row
'displaying result in a message box
MsgBox (Non_Empty_Cell)
End Sub
```

- Run the code from the
**Run**tab.

- We will get the row number of the last non-empty cell in a message box.

**Download the Practice Workbook**

**Related Articles**

- How to Select Cells in Excel Using Keyboard
- Multiple Excel Cells Are Selected with One Click
- How to Select Multiple Cells in Excel Without Mouse
- How to Select Cells in Excel Without Dragging
- How to Select Large Data in Excel Without Dragging

**<< Go Back to Select Cells | Excel Cells | Learn Excel**