Hello Rashid,

Am Wed, 15 Jun 2011 10:40:25 -0700 (PDT) schrieb prkhan56:

I am using Excel 2007 and have a following problem. I have several
hundred cols of Data with variable entries.

for eg

City Town State ... ...... ...... ...... ....
Dubai Karama UAE
Sharjah Rolla
Ajman

The macro when run should create range name for all active area in the
sheet (until the last col) using the Header Row (eg. City, Town,
State, ...., ......., ......., .........).

try:
Sub myNames()
Dim LCol As Integer
Dim LRow As Long
Dim i As Integer

With ActiveSheet
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LCol
LRow = .Cells(Rows.Count, i).End(xlUp).Row
ActiveWorkbook.Names.Add Name:=.Cells(1, i), _
RefersTo:=.Range(.Cells(1, i), .Cells(LRow, i))
Next
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2