# Thread: Find last value in a column

1. ## Find last value in a column

Dear Loungers,

just can't get the formula right for this.

I have a column with values in, they are numbers but in the form of text i.e. with a single quotation mark in front like this '0.01. I want to find the last value in the column - the column will have an increasing number of values over time but not more than 200 so the range will be e.g. C4:C200. the data could look like this:
C4 = '0.01
C5 = '0.02
C6 = '1.0
C7 = blank

So in this case I want to return '1.00

Can anyone help?

Thank you............................ liz

2. Lizat,

I know you are looking for a formula but a quick resolution could be a macro. Change the 3 in both lines to the column number you wish to search for.

HTH,
Maud

Code:
```Public Sub GetLastNum()
LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'CHANGE THE 3 TO THE COLUMN WITH VALUES
MsgBox Cells(LastRow, 3).Value  'CHANGE THE 3 TO THE COLUMN WITH VALUES
End Sub```

3. Hi Maud,

Thank you.

The reason i wanted a formula is that i need to do it several times and display the result in a cell at the top of the column in question. I am trying it with index and match but never quite do it right so I am hoping someone will help - likely to be RetiredGeek who is one of the most generous members here

liz

4. This seems to work:

=INDEX(E4:E200,COUNTA(E4:E200),1)

It doesn't cope if the sequence has cells with blank in them but is good enough

5. Liz,

Thanks for the kind words. I guess I'll have to come up with an answer.

Actually, I think Maud was on the right track but instead of a Sub use a function.
Code:
```Option Explicit

Public Function GetLastNum(lCol As Long) As Double
Application.Volatile
GetLastNum = ActiveSheet.Cells(Rows.Count, lCol).End(xlUp).Value
End Function```
Volatile.JPG
The advantages of using this function vs formula:
1. Easier to type so less prone to input errors.
2. Is not affected by blank rows
3. Not limited to the number of rows so you don't have to worry about going over the limit and forgetting to change the formula

Syntax:
=GetLastNum(Column Number)

HTH

6. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

lizat (2013-04-03)

7. fab!!!!

8. Hi lizat

Try the LOOKUP function:
=LOOKUP(2,1/(C:C<>""),C:C)

Kevin

9. Hi

Kevin: can't get yours to work.
RG: nice function, but if you use your function in column D (as per your example), then you must always make sure there's at least one value below it in the same column! (or you'll get the circular error message).
And to use it for returning values from say column PKQ (what number is that???) I would use your function like this:
=GetLastNum(Column(PKQ1))
..by clicking any cell in the required column e.g. cell PKQ1

Now, for those of you who are not allowed to have macro-enabled files (and so cannot use custom functions), you are stuck with something like the following, which will return the last value in a specified column, whether the last value is numeric or text and whether the column includes blanks or not:
=INDEX(A:A,MAX(MATCH(9.99E+307,A:A),MATCH(REPT("z" ,255),A:A)))

..using column [A] as the example.
The formula will return #N/A if the specified column is completely empty.

zeddy

10. Hi zeddy

See the attached file.

Kevin

11. Zeddy,

=GetLastNum(Column(PKQ1))
Love it! Thanks.

=INDEX(A:A,MAX(MATCH(9.99E+307,A:A),MATCH(REPT("z" ,255),A:A)))
Proves my point 1.

Ok, I added the error check so it returns zero if there are no other rows.
Code:
```Option Explicit

Public Function GetLastNum(lCol As Long) As Double

Dim lActiveRows As Long
Application.Volatile

lActiveRows = _
Cells(Rows.Count, lCol).End(xlUp).Row

If lActiveRows > 1 Then
GetLastNum = ActiveSheet.Cells(Rows.Count, lCol).End(xlUp).Value
Else
GetLastNum = 0
End If

End Function```
Volatile.JPG
It will even work if it's not in row 1!

12. Hi RG

Your point is proved of course.
I agree with you.
If you are allowed to have a macro-enabled file that is.

I guess you spotted that my formula combines finding the row of the last text value, and the row of the last numeric value, and then just returning the last entry.
Of course you could simplify the formula if you only wanted the last number. Or if you only wanted the last text entry.

zeddy

13. RG,

Yes, a function instead of a sub is definitely the way to go here. Application.Volatile is command I should use more often in my functions. Thanks for the reminder.

Maud

14. Hi Kevin

I now like your formula very much.
But check out my attached files.
It didn't work in one file, but did in the other!
Go figure!!

zeddy

15. If all the numbers are stored as text, then
=LOOKUP("A",C2:C400)
should work

16. Hi zeddy

Strange that, as both files work for me!

Kevin

Originally Posted by zeddy
Hi Kevin

I now like your formula very much.
But check out my attached files.
It didn't work in one file, but did in the other!
Go figure!!

zeddy

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•