How to: User selected range?

P

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

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

Gary''s Student

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

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

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
 
P

Phil Smith

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

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
 
Top