Need value of last number in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron Smith - 23 Jan 2008 01:46 GMT

I have a column of numbers (stock prices) that I am updating daily. I want
to use the last value I entered in the column to calculate my losses (current
price - fixed beginning stock price). I can get the Row number with the ROW
function, but am unsuccessful when trying to use this number in a Cell
reference:
i.e. +C=ROW(A55) -- where C is the column and =ROW(A55) will return 55
(contents of Cell 55 being what I am after).
Is there a way I can get this Cell reference to work or is there another
function or method I can use to determine the last value in a column.

Signature

Ron Smith in Round Rock

Reply to this Message

Mark Ivey - 23 Jan 2008 03:53 GMT

Give this snippet a go...

Assign it to a variable: myCellValue = Cells(Rows.Count,
3).End(xlUp).Value

Then you can utilize "myCellValue" somewhere else in your code.

If you are looking for a user defined function, take a look at the following
from http://www.mvps.org/dmcritchie/excel/lastcell.htm

' Apply this function to a new module in your workbook
Function LastInColumn(rng As Range) As Variant
LastInColumn = Cells(Rows.Count, rng(1).Column).End(xlUp).Value
End Function

Then you can reference it on your worksheet with the following function
call:

=LastInColumn(C:C)

> I have a column of numbers (stock prices) that I am updating daily. I
> want
[quoted text clipped - 8 lines]
> Is there a way I can get this Cell reference to work or is there another
> function or method I can use to determine the last value in a column.

Reply to this Message