Excel - how to find last nonblank entry?

[NickC]

I thought this would be easy, but it's eluded me!

If I have a column of data, how do I extract the last entry in that column? I want this to be a variable so that as I add entries, it gets updated - i.e., to make a running list.

For example - if my data looks like this:

A1 is 5
A2 is 12
A3 is 30

Then I want B1 to show 30. However, if I add an entry A4=55, then B1 should change to be 55.

It's weird because I can easily set B1 to be the MIN of my range, say A1 to A50. I can set B1 to be the MAX or AVG of this range. But I can't find a function to show the last entry in the list.

[oldhermit]

You have a selection.
This is the simplest, but not as reliable as the second example.
If there is an empty cell before reaching the last value, then the results WILL be faulty.
Use formula =FINDLVAL(<range>)

Function FINDLVAL(RNG As Range) As Variant
Dim c As Object
    For Each c In RNG
        If (c.Value <> "") Then FINDLVAL = c.Value Else Exit For
    Next c
End Function

This one is more reliable when there are mixed cell values.
It also reads the Range backwards to find the last cell with a value.

Function FINDLASTVAL(RNG As Range) As Variant
Dim c As Object
Dim DI(), i, j, VAL
    ' Count the cells
    i = 0
    For Each c In RNG
        i = i + 1
    Next c
    ' Map the cells with numeric values in memory
    ReDim DI(i)
    j = 0
    For Each c In RNG
        VAL = c.Value
        If (IsNumeric(VAL) = True) Then DI(j) = VAL Else DI(j) = ""
        j = j + 1
    Next c
    ' Find the last value in the assigned range
    For j = UBound(DI()) To LBound(DI()) Step -1
        VAL = DI(j)
        If (VAL <> "") Then Exit For
    Next j
    Erase DI()
    FINDLASTVAL = VAL
End Function

Use formula =FINDLASTVAL(<range>)

[NickC]

Wow - thanks, I'll try those out. I figured there'd just be a variable or function!