You can get to the last cell in a single area range with something like this:

dim myRng as range
set myRng = range("a1:b99")
msgbox myrng.cells(myrng.cells.count).value

(if it doesn't have an error in it.)

And if the range can contain multiple areas, you can go directly to end of the
last area with something like:

Option Explicit
Function lastValueInArray(table_array As Range) As Variant
Dim res As Variant

With table_array
With .Areas(.Areas.Count)
If IsError(.Cells(.Cells.Count).Value) Then
res = .Cells(.Cells.Count).Text
Else
res = .Cells(.Cells.Count).Value
End If
End With
End With

lastValueInArray = res
End Function

'tested with:
Sub testme()
MsgBox lastValueInArray(Selection)
End Sub

Bart Deschoolmeester wrote:

Hello,
I always think: oh I can do that quickly in a couple of
minutes and then it seems to take me houres...

So here's what happened this time:
I programmed a small loop but excel seems to loop through
it a more than the possible maximum and somtimes gives
unexpected results.
Maybe it has something to do with formulas:
When the values of the array (range) are not formulas: it
works ok. But if there are formulas...

Function lastvalueinarray(table_array As Range) As Variant
Dim i As Integer
Application.Volatile
i = 1
MsgBox (table_array.Rows.Count)
While i < table_array.Rows.Count
MsgBox (i)
MsgBox (table_array.Item(i, 1).Value)
i = i + 1
Wend
lastvalueinarray = table_array(i, 1).Value
End Function

The messageboxes are there as a sort of debugging.

One should expect that the maximum number of loops
is 'count'. So there would appear 2xcount messageboxes.
But it is a multiple of that. On top of that: If the array
(range) contains #N/A as result of a formula the result is
#Value! instead of the last value in the array.

I know there are easier ways of getting the last value.
But this provides an easy way of showing and explaining
the problem whithout posting 2 pages of code.

Thanks

--

Dave Peterson