How to: User selected range?

[Phil Smith]

My goal is to have a spreadsheet with a few thousand rows of data,
including a coloum of unique numbers. User should be able to select by
dragging a range, ad get in return, a comma deliminated list of all of
the numbers in that range. I have the second part ready. A function
which you give a range, and it returns the list.

Now, how do I get that range from the user? Ideally, the formula using
that function would use a range name, so my problem becomes how to get a
user to select a range and hit a macro button, or hit a macro button and
select a range, with the selected range being assigned that particular
name...

 

[Gary''s Student]

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
r.Select
MsgBox (r.Address)
End Sub

This will work whether the user types the range in the box or uses the mouse.

 

[Phil Smith]

Great, but how do I assign that range to MyRangeName?

 

[Gary''s Student]

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
End Sub

 

[Phil Smith]

Nope. It runs with no errors, but the cells covered by that range do
not change to the selected cells. The range remains the same...

 

[Gary''s Student]

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
MyRangeName.Select
End Sub

 

[Phil Smith]

Still no joy. Runs without errors, but MyRangeName is not being updated
to reflect the choice...

 

[Phil Smith]

Never mind, I got it:

Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
ThisWorkbook.Names.Add Name:="MyNameRange", _
RefersTo:=r, Visible:=True
End Sub