Last cell within an address
 

Hello,

Does anyone know the easiest way (i.e. most
efficient/fewest code lines/fastest code, etc.) to select
the S.E. corner or bottom right cell address from a single
area range address stored in a variable?

For example, if my variable contains: strRange = "$2:$4"
My function would return the string: "$IV$4"

OR,

If my variable contains: strRange = "$BA457:FJ1057"
My function would return the string: "$FJ$1057"

Your example code would be most appreciated. Thanks much
for your assistance.

Last cell within an address
 

Raj,

Here ya go!


Using a range parameter (better):

Function LastCellFromRange(rng As Range)
LastCellFromRange = rng.Resize(1, 1).Offset(rng.Rows.Count - 1,
rng.Columns.Count - 1).Address
End Function

Using a string parameter:

Function LastCellFromString(sRng As String)
LastCellFromString = Range(sRng).Resize(1,
1).Offset(Range(sRng).Rows.Count - 1, Range(sRng).Columns.Count - 1).Address
End Function


--
Charles
www.officezealot.com


"raj" wrote in message
...
Hello,

Does anyone know the easiest way (i.e. most
efficient/fewest code lines/fastest code, etc.) to select
the S.E. corner or bottom right cell address from a single
area range address stored in a variable?

For example, if my variable contains: strRange = "$2:$4"
My function would return the string: "$IV$4"

OR,

If my variable contains: strRange = "$BA457:FJ1057"
My function would return the string: "$FJ$1057"

Your example code would be most appreciated. Thanks much
for your assistance.

Last cell within an address
 

How about...

Sub ReturnLastCellAddress()
Dim strRange As Range
Set strRange = Range("$2:$4")
MsgBox strRange(strRange.Count).Address
End Sub


--
Regards,
Rocky McKinley


"raj" wrote in message
...
Hello,

Does anyone know the easiest way (i.e. most
efficient/fewest code lines/fastest code, etc.) to select
the S.E. corner or bottom right cell address from a single
area range address stored in a variable?

For example, if my variable contains: strRange = "$2:$4"
My function would return the string: "$IV$4"

OR,

If my variable contains: strRange = "$BA457:FJ1057"
My function would return the string: "$FJ$1057"

Your example code would be most appreciated. Thanks much
for your assistance.