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...

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.
--
Gary''s Student - gsnu200821


"Phil Smith" wrote:

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...

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




Gary''s Student wrote:
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.

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

--
Gary''s Student - gsnu200821


"Phil Smith" wrote:

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




Gary''s Student wrote:
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.

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 wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
End Sub

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

--
Gary''s Student - gsnu200821


"Phil Smith" wrote:

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 wrote:
Sub servient()
Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)
Set MyRangeName = r
End Sub