Hi, all

Is any method could to return the last cell's address within current
selection?

ex: i select from A2 to B5, then it will return B5


Thanks

Option Explicit
Sub test1()

Dim myRng As Range
Set myRng = ActiveSheet.Range("a2:b5")
With myRng
MsgBox .Cells(.Cells.Count).Address
End With

End Sub

'multi-area range????
Sub test2()

Dim myRng As Range
Set myRng = ActiveSheet.Range("a2:b5,x9:z12")

With myRng
With .Areas(.Areas.Count)
MsgBox .Cells(.Cells.Count).Address
End With
End With

End Sub

OKLover wrote:

Hi, all

Is any method could to return the last cell's address within current
selection?

ex: i select from A2 to B5, then it will return B5

Thanks

--

Dave Peterson

Cool ~ Dave! you are my hero. :D

when i select from a2 to d5 it returns d5 <== this is correct.
but when i select from d5 to a2, it should returns a2 not d5


"Dave Peterson" wrote:

Option Explicit
Sub test1()

Dim myRng As Range
Set myRng = ActiveSheet.Range("a2:b5")
With myRng
MsgBox .Cells(.Cells.Count).Address
End With

End Sub

'multi-area range????
Sub test2()

Dim myRng As Range
Set myRng = ActiveSheet.Range("a2:b5,x9:z12")

With myRng
With .Areas(.Areas.Count)
MsgBox .Cells(.Cells.Count).Address
End With
End With

End Sub

OKLover wrote:

Hi, all

Is any method could to return the last cell's address within current
selection?

ex: i select from A2 to B5, then it will return B5

Thanks

--

Dave Peterson

OK, I got the answer by myself as below:

Sub test2()

With Selection.Areas(Selection.Areas.Count)
If ActiveCell.Address = .Cells(.Cells.Count).Address Then
Debug.Print .Cells(1).Address
Else
Debug.Print .Cells(.Cells.Count).Address
End If
End With

End Sub

"OKLover" wrote:

Cool ~ Dave! you are my hero. :D

when i select from a2 to d5 it returns d5 <== this is correct.
but when i select from d5 to a2, it should returns a2 not d5


"Dave Peterson" wrote:

Option Explicit
Sub test1()

Dim myRng As Range
Set myRng = ActiveSheet.Range("a2:b5")
With myRng
MsgBox .Cells(.Cells.Count).Address
End With

End Sub

'multi-area range????
Sub test2()

Dim myRng As Range
Set myRng = ActiveSheet.Range("a2:b5,x9:z12")

With myRng
With .Areas(.Areas.Count)
MsgBox .Cells(.Cells.Count).Address
End With
End With

End Sub

OKLover wrote:

Hi, all

Is any method could to return the last cell's address within current
selection?

ex: i select from A2 to B5, then it will return B5

Thanks

--

Dave Peterson