find cell address of last active cell in range

[Dave F]

I'm trying to come up with a UDF, which I call endaddress() that will
return the cell address of the last active (used) cell in a range.

Example: assume cells A2:A5 have text/numbers in them. I want to be
able to enter in another cell =endaddress(A3) and have $A$5 be
returned.

This is what I've managed to come up with (from feedback given to me
in an earlier posting on this topic):

Function EndAddress(r As Range) As String
EndAddress = ""
For Each rr In r
If IsEmpty(rr) Then
Else
EndAddress = rr.Address
End If
Next
End Function

However, this merely returns the cell address I specify in the
function. If I enter =endaddress(A3) then $A$3 is returned, even if
that's not the last active cell in the range.

Any suggestions on how to modify this?

Thanks.

 

[Guest]

The function only analyzes the cells in the range that is passed to it.

Try =EndAddress(A:A)

 

[Dave D-C]

Dave,
This gives $A$5 as requested.
But what would you want if a1:c3 were
X X b ' (b is blank/null)
X b b
b b b
and you called EndAddress(Range("A1"))
Would you want $A$2 or $B$1 or something else?

Function EndAddressCol(r As Range) As String
EndAddressCol = r.End(xlDown).Address
End Function ' Dave C D-C