Let's say a user selects ranges A4:A10. In VBA, how can I find out where
the range is ending (i.e. "A10")?

I'm trying to write code that lets the user delete rows within certain
boundaries, so I need to know if the user has selected a range that falls
partially out of bounds -- for instance, he/she can only delete rows within
A4:A8, so I need to trap if he/she selects outside A8 and give an error
message.

How do I detect the end of the user-selected range?

Thanks.

Syed,

Selection.SpecialCells(xlCellTypeLastCell).Address

Neil

"Syed Faisal" wrote in message
...
Let's say a user selects ranges A4:A10. In VBA, how can I find out where
the range is ending (i.e. "A10")?

I'm trying to write code that lets the user delete rows within certain
boundaries, so I need to know if the user has selected a range that falls
partially out of bounds -- for instance, he/she can only delete rows
within
A4:A8, so I need to trap if he/she selects outside A8 and give an error
message.

How do I detect the end of the user-selected range?

Thanks.

Sub Tester11()
Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count = 1 Then
Set rng1 = rng(rng.Count)
MsgBox rng1.Address
End If

End Sub


But, your test might be

set rng = Range("A4:A8") 'allowed range
if union(selection,rng).Address < rng.Address then
msgbox "out of bounds"
End if


--
Regards,
Tom Ogilvy

"Syed Faisal" wrote in message
...
Let's say a user selects ranges A4:A10. In VBA, how can I find out where
the range is ending (i.e. "A10")?

I'm trying to write code that lets the user delete rows within certain
boundaries, so I need to know if the user has selected a range that falls
partially out of bounds -- for instance, he/she can only delete rows
within
A4:A8, so I need to trap if he/she selects outside A8 and give an error
message.

How do I detect the end of the user-selected range?

Thanks.