Finding last cell with a value when range contains blanks

Dave L

Board Regular
Joined
Jun 19, 2003
Messages
87
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
or if you want to find the position of the last value, rather than the value itself

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

assumes your "values" are numeric
 
Upvote 0
Barry,

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

Dave
 
Upvote 0
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
 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,223,153
Messages
6,170,395
Members
452,324
Latest member
stuart1980

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top