find cell address of last active cell in range

  • Thread starter Thread starter Dave F
  • Start date Start date
D

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.
 
The function only analyzes the cells in the range that is passed to it.

Try =EndAddress(A:A)
 
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
 
Back
Top