# Finding last cell with a value when range contains blanks

#### Dave L

##### Board Regular
I would like to do this with a formula instead of VBA if possible. I have a single column of data that is being calculated. I would like to find the last cell that has a value in it. The problem is that every other cell as you go down the column will be blank, even though there is a formula in each cell. It would look kind of like this:

1 Value
2
3 Value
4
5 Value
6
7 Value
8
9
10

So for the range of A1 to A10, I need a formula to tell me that A7 is the last one with a value. As you can see I can't just look for the first blank because that would be A2.

Any help would be greatly appreciated.

Thanks,

Dave

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### NBVC

##### Well-known Member
Try

=LOOKUP(9.99999999999999E+307,A1:A10)

#### Oaktree

##### MrExcel MVP
=LOOKUP(9.99999999999999E+307,A1:A10)

#### barry houdini

##### MrExcel MVP
or if you want to find the position of the last value, rather than the value itself

=MATCH(9.99999999999999E+307,A1:A10)

#### Dave L

##### Board Regular
Barry,

Your position formula is exactly what I needed - it works great. Thank you all for your suggestions.

Dave

#### Dave L

##### Board Regular
Ok, i have a new twist on this one. Is it also possible to find the position of the FIRST cell that has a value? As usual you solve one problem only to find another one.

Thanks,

Dave

#### just_jon

##### Legend
=MIN(IF(LEN(A1:A25),ROW(A1:A25)))

Where A1:A25 is the range of interest.

The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

#### Dave L

##### Board Regular
just_jon,

That did the trick. It works perfect.

Dave

Replies
1
Views
142
Replies
1
Views
539
Replies
3
Views
796
Replies
7
Views
304
Replies
23
Views
849

1,195,590
Messages
6,010,612
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back