Excel VBA retrieves a range of a user-selected range with the mouse

Question

This is not a usedrange problem.
For example, in Excel, the user selects a range (possibly empty) with the mouse, say B4: C12

And let's say after that, without deselecting the range, the user clicks the macro, and the macro should indicate B4: C12 .

Can someone show an example?

The macro should consist of the following lines:

 Sub showrng() MsgBox SelectedRange.Address(ReferenceStyle:=xlA1) End Sub 

Answer 1

 Sub macro1() MsgBox Selection.Address(ReferenceStyle:=xlA1, _ RowAbsolute:=False, ColumnAbsolute:=False) End Sub 

NTN!

Answer 2

 Sub macro1() MsgBox Selection.Address End Sub 

or

 Sub macro1() Dim addr as String addr = Selection.Address msgbox addr ' Now, as we found the address, according to that... you can also do other operations End Sub 

Answer 3

Since the selection may include several independent ranges, the following code shows a more complete solution to the problem:

 Public Sub SelectionTest() Dim r As Range Dim s As String Select Case Selection.Areas.Count Case 0: MsgBox "Nothing selected." Case 1: MsgBox "Selected range: " & Selection.Areas(1).Address(False, False) Case Else s = "" For Each r In Selection.Areas s = s + vbNewLine + r.Address(False, False) Next r MsgBox "Selected several areas:" & s End Select End Sub