I have a list on a worksheet. I would like to convert that list to a named
range. Is there a VBA procedure that would do so?

For example, in cells A1:A3 I have

A1 -> Jan
A2 -> Feb
A3 -> Mar

I would like to convert this list to a defined name called "Months". The
defined name dialog box "refers to" input for the name "Months" would look
like:

={"Jan","Feb","Mar"}

Thanks for your help.

--
Steph

I think this does what you want...

Sub CreateNameArray()
Dim X As Long
Dim R As Range
Dim N As String
Set R = Worksheets("Sheet4").Range("A1:A3")
N = "={"
For X = 1 To R.Count
N = N & """" & R(X).Value & ""","
Next
N = Left(N, Len(N) - 1) & "}"
Names.Add Name:="Months", RefersTo:=N
End Sub

--
Rick (MVP - Excel)


"Steph" <(E-Mail Removed)> wrote in message
news:25BE7CDF-4005-4ECA-B689-(E-Mail Removed)...
>I have a list on a worksheet. I would like to convert that list to a named
> range. Is there a VBA procedure that would do so?
>
> For example, in cells A1:A3 I have
>
> A1 -> Jan
> A2 -> Feb
> A3 -> Mar
>
> I would like to convert this list to a defined name called "Months". The
> defined name dialog box "refers to" input for the name "Months" would look
> like:
>
> ={"Jan","Feb","Mar"}
>
> Thanks for your help.
>
> --
> Steph

You actually don't need a VBA procedure to define a named range. You can
just select the range and define the name attached to it. If the range is
varying in size over time, you can define a dynamic range. When you
reference it, do something like this

Dim myRange as Excel.Range
Dim myWS as Excel.worksheet

Set myWS = Worksheets("Sheet1") 'or whatever sheet it's on
Set myRange = myws.Range("Months")


"Steph" wrote:

> I have a list on a worksheet. I would like to convert that list to a named
> range. Is there a VBA procedure that would do so?
>
> For example, in cells A1:A3 I have
>
> A1 -> Jan
> A2 -> Feb
> A3 -> Mar
>
> I would like to convert this list to a defined name called "Months". The
> defined name dialog box "refers to" input for the name "Months" would look
> like:
>
> ={"Jan","Feb","Mar"}
>
> Thanks for your help.
>
> --
> Steph

???

range("a1:a12").name="Months"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Steph" <(E-Mail Removed)> wrote in message
news:25BE7CDF-4005-4ECA-B689-(E-Mail Removed)...
>I have a list on a worksheet. I would like to convert that list to a named
> range. Is there a VBA procedure that would do so?
>
> For example, in cells A1:A3 I have
>
> A1 -> Jan
> A2 -> Feb
> A3 -> Mar
>
> I would like to convert this list to a defined name called "Months". The
> defined name dialog box "refers to" input for the name "Months" would look
> like:
>
> ={"Jan","Feb","Mar"}
>
> Thanks for your help.
>
> --
> Steph

Rick,

Thank you. That's exactly what I was looking for.

--
Steph


"Rick Rothstein" wrote:

> I think this does what you want...
>
> Sub CreateNameArray()
> Dim X As Long
> Dim R As Range
> Dim N As String
> Set R = Worksheets("Sheet4").Range("A1:A3")
> N = "={"
> For X = 1 To R.Count
> N = N & """" & R(X).Value & ""","
> Next
> N = Left(N, Len(N) - 1) & "}"
> Names.Add Name:="Months", RefersTo:=N
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "Steph" <(E-Mail Removed)> wrote in message
> news:25BE7CDF-4005-4ECA-B689-(E-Mail Removed)...
> >I have a list on a worksheet. I would like to convert that list to a named
> > range. Is there a VBA procedure that would do so?
> >
> > For example, in cells A1:A3 I have
> >
> > A1 -> Jan
> > A2 -> Feb
> > A3 -> Mar
> >
> > I would like to convert this list to a defined name called "Months". The
> > defined name dialog box "refers to" input for the name "Months" would look
> > like:
> >
> > ={"Jan","Feb","Mar"}
> >
> > Thanks for your help.
> >
> > --
> > Steph
>
>