I want to create range names from column A to BK and from 2 to 7000
rows with name as text in the first cell in the respective column.
I know how to create a name for one column but for so many columns I am
struck.

Can any one guide me please?

Regards,
Madiya.

One way:

Dim rng as Range
set rng =Range(cells(1,1),Cells(1,63))
for each cell in rng
rng.offset(1,0).Resize(6999,1).Name = cell.Text
Next

another

Range(A1:BK7000).CreateNames Top:=True, _
Left:=False, Bottom:=False, Right:=False

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy



"Madiya" wrote:

I want to create range names from column A to BK and from 2 to 7000
rows with name as text in the first cell in the respective column.
I know how to create a name for one column but for so many columns I am
struck.

Can any one guide me please?

Regards,
Madiya.

Hi Tom,
You are always quick and kind to me. Thanks.
One more question.
How can I use it to create names in other workbook?

for e.g.
='[TEST.xls]Master Data'!$B$2:$B$7000

Regards,
Madiya

Tom Ogilvy wrote:

One way:

Dim rng as Range
set rng =Range(cells(1,1),Cells(1,63))
for each cell in rng
rng.offset(1,0).Resize(6999,1).Name = cell.Text
Next

another

Range(A1:BK7000).CreateNames Top:=True, _
Left:=False, Bottom:=False, Right:=False

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy



"Madiya" wrote:

I want to create range names from column A to BK and from 2 to 7000
rows with name as text in the first cell in the respective column.
I know how to create a name for one column but for so many columns I am
struck.

Can any one guide me please?

Regards,
Madiya.

Sub AddNames()
Dim rng As Range, rng1 as Range, cell as Range
With Workbooks("Test.xls").Worksheets("Master Data")
Set rng = .Range(.Cells(1, 1), .Cells(1, 63))
End With
For Each cell In rng
Set rng1 = rng.Offset(1, 0).Resize(6999, 1)
ThisWorkbook.Names.Add Name:=cell, _
RefersTo:="=" & rng1.Address(1, 1, xlA1, external:=True)
Next
End Sub

--
Regards,
Tom Ogilvy


"Madiya" wrote:

Hi Tom,
You are always quick and kind to me. Thanks.
One more question.
How can I use it to create names in other workbook?

for e.g.
='[TEST.xls]Master Data'!$B$2:$B$7000

Regards,
Madiya

Tom Ogilvy wrote:

One way:

Dim rng as Range
set rng =Range(cells(1,1),Cells(1,63))
for each cell in rng
rng.offset(1,0).Resize(6999,1).Name = cell.Text
Next

another

Range(A1:BK7000).CreateNames Top:=True, _
Left:=False, Bottom:=False, Right:=False

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy



"Madiya" wrote:

I want to create range names from column A to BK and from 2 to 7000
rows with name as text in the first cell in the respective column.
I know how to create a name for one column but for so many columns I am
struck.

Can any one guide me please?

Regards,
Madiya.