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 F" wrote:

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.

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

Dave F wrote:
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.


----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----