Users often need to find the last non-empty cell of a column. In a long dataset, it is quite time-consuming to find the last non-empty cell or the value of that cell. In this article, we will discuss ** 9** ways of how to go to the last non empty cell in a column in

**.**

*Excel***Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 9 Handy Ways by Which Excel Go to Last Non Empty Cell in Column

In this article, we will learn **9** ways to go to the last non-empty cell in Excel. Firstly, we will use ** the XMATCH function.**Â Secondly, we will go for

**. Thirdly, we will utilize a keyboard shortcut. Then, we will use a combination of the**

*the XLOOKUP function*

*SUMPRODUCT, MAX,**and*

**. After that, we will combine**

*ROW functions*

*the INDEX, MAX,**and*

**. Afterward, we will apply**

*ROW functions***together. Next, we will use**

*the OFFSET and COUNTA functions*

*the INDEX**and*

**in conjunction to get the result. In the penultimate method, we will opt for**

*COUNTA functions***to do our job. Finally, we will resort to a**

*the LOOKUP function***code to go to the last non-empty cell in a column in Excel.**

*VBA*### 1. Using 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. In this method, we will use this feature of the function to get the position of the last non-empty cell in a column in excel.

**Steps:**

- Firstly, select the
cell and write the following formula:*E5*

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

- Then, hit
.*Enter*

- Consequently, we will find the row number of the last nonempty cell of the column.

Here, 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,

**, indicates the range of cells the function will look for values. The next argument,**

*B:B***, denotes**

*2***. This match is employed when the exact content of the cell is not known. Finally, the**

*Wildcard character match***means it will match from bottom to top. Thus, it will find the last nonempty value in the dataset**

*-1*### 2. Applying XLOOKUP Function

To find items in a table or range by row, we use ** the XLOOKUP function**. In this method, we will use this excel function to find the value of the last non-empty cell of a column.

**Steps:**

- To begin with, select the
cell and write the following formula:*E5*

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

- Then, press
.*Enter*

.

- As a result, the function will return the content of the last nonempty cell in the column.

Here, the first argument is a ** Wildcard character**. It is used when we do not know the content of a cell. The next argument is the

**. In this case, it is**

*look_array***. The third argument is the**

*B:B***. It is also**

*return_array***, as we will return the value from the same array. The next argument is a blank character. If the function finds no match, it will return the blank character. The following argument,**

*B:B***, stands for**

*2***. When the exact contents of the cell are unknown, this match is used. The final**

*Wildcard character match***indicates that the order will match from bottom to top. Thus, it will find the last nonempty value in the dataset**

*-1*### 3. Using Keyboard Shortcut

In this instance, we will use a keyboard shortcut **Ctrl+Down Arrow** to go to the last nonempty cell of a column.

**Steps:**

- Firstly, select the first cell of the column.
- In this case, we will select the
**B5** - Then, hit
.*Ctrl+Down Arrow*

- Consequently, we will reach the last nonempty cell.

**Note:**

- This method is applicable only when there is no empty row in between the datasets.

**Read More:** **How to Select Cells in Excel Using Keyboard (9 Ways)**

### 4. Combining SUMPRODUCT, MAX, and ROW Functions

** The SUMPRODUCT function **returns the sum of the products of the ranges or arrays that are given as argument of the function. In this method, we will combine it with

*the MAX**and*

**to get the position of the last non-empty cell in a column in excel.**

*ROW functions***Steps:**

- To start with, select the
cell and write the formula below,*E5*

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

- Then, hit
.*Enter*

- As a result, we will get the row number of the last nonempty cell.

đź”Ž **Formula Breakdown:**

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

**Similar Readings**

**How to Select All Rows in Excel (6 Easy Ways)****How to Select Data in Excel for Graph (5 Quick Ways)****[Fixed!] Selected Cells Not Highlighted in Excel (8 Solutions)****How to Select Only Filtered Cells in Excel Formula (5 Quick Ways)****How to Select Visible Cells in Excel (5 Quick Tricks)**

### 5. Using Combination of INDEX, MAX, and ROW Functions

** The INDEX function** extracts a value from a table or range, or a reference to a value, and returns it. In this example, we will combine

**to get the value of the last cell in a column.**

*the INDEX, MAX, and ROW functions***Steps:**

- To start with, write the following formula after selecting the
cell,*E5*

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

- Then, hit the
key.*EnterÂ*

- As a result, the value of the last nonempty cell will be on the screen.

đź”Ž **Formula Breakdown:**

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

### 6. Applying OFFSET and COUNTA Functions Together

** The OFFSET function **returns a reference to a range that is separated from a cell or a range of cells by a specified number of rows and columns.

**returns the number of cells that are not empty. Here, we will combine these to get the value of the last non-empty cell in a column.**

*The COUNTA function***Steps:**

- First, select the
cell and write the following formula down,*E5*

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

- Then, press the
button.*EnterÂ*

- Consequently, we will get the value of the last nonempty cell in the
cell.*E5Â*

đź”Ž **Formula Breakdown:**

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

### 7. Combining INDEX and COUNTA Functions

With the help of** the INDEX function**, you can retrieve a value from a table, a range, or a reference to a value. In this instance, we will use this function together with

**.**

*the COUNTA function***Steps:**

- Initially, choose cell
and enter the following formula:*E5*

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

- Then, hit
.*Enter*

- We will therefore obtain the value of the final nonempty cell in the
cell.*E5Â*

đź”Ž** Formula Breakdown:**

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

### 8. Using LOOKUP Function

In this method, we will use ** the LOOKUP function** to go and find the value of the last nonempty cell in a column.

**searches for a reference indicated in the argument and returns the value of the reference cell.**

*The LOOKUP function***Steps:**

- At the start, select the
cell and type the following formula in the cell,*E5*

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

- Then, hit
.*Enter*

- Consequently, we will get the value of the last nonempty cell.

*Here, the*

**searches through the**

*LOOKUP function***range as indicated by the third argument. It searches if the cells are empty or not through the expression â€ś**

*B:B***â€ť.When it assigns**

*B:B<>â€ťâ€ť***if the cell is non-empty and**

*TRUE***otherwise. It returns the result as an array of**

*FALSE***and**

*TRUE***. Then, it turns the**

*FALSE***and**

*TRUE***into**

*FALSE***and**

*1***respectively. Finally, in the**

*-1***expression, the**

*â€ś1/(B:B<>â€ťâ€ť)â€ť***and**

*1â€™s***in the array divide the**

*-1â€™s***value and return an array of**

*1***and**

*1â€™s***.Then,**

*-1â€™s***looks for**

*the INDEX function***in the**

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

*B:B***.**

*Â â€śTomâ€ť*### 9. Applying VBA Code

In the final method, we will use a simple **VBA **code to go to the last non empty cell in a column in * Excel*.

**Steps:**

- Firstly, select the dataset.
- Then, go to the
tab in the ribbon.*Developer* - From there, select the
tab.*Visual BasicÂ* - Consequently, the
window will be opened.*Visual Basic*

- After that, in the
tab, click on*Visual Basic*.*Insert* - Then, select the
tab.*ModuleÂ* - Consequently, a coding module will appear.

- In the coding module, write down the following code.
- Then, save the code.

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

- Then, run the code from the
tab.*RunÂ*

- Consequently, we will see the row number of the last nonempty cell in a message box.

**Read More:** **Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)**

## Conclusion

In a long excel dataset, it takes a significant amount of time to go to the last non-empty cell in a column. These methods will allow users to go to the last nonempty cell and fetch the value or the row number of the cell in no time in * Excel*.

**Related Articles**

**How to Select Multiple Cells in Excel (7 Quick Ways)****How to Select Row in Excel If Cell Contains Specific Data (4 Ways)****Select All Cells with Data in Excel (5 Easy Methods)****[Solved!] CTRL+END Shortcut Key Goes Too Far in Excel (6 Fixes)****Select a Range of Cells in Excel Formula (4 Methods)****How to Select Multiple Cells in Excel without Mouse (9 Easy Methods)**