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

On Jun 15, 10:02*pm, Claus Busch wrote:
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 createrangenamefor 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.AddName:=.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

Hello Claus
Thanks for the code.
Can we have a check (similar to the code on Contextures) given below
to look for Blanks and Col Header with spaces

' if a column header contains spaces,
' replace the space with an underscore
' spaces are not allowed in range names.
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName = "" Then
' if column header is blank, warn the user and
' stop the macro at that point
' names will only be created for those cells with text in
them.

Regards
Rashid

Hi Rashid,

try:

Sub myNames()
Dim LCol As Integer
Dim LRow As Long
Dim i As Integer
Dim rngName As String

With ActiveSheet
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LCol
If .Cells(1, i) = "" Then
MsgBox "Enter a header in column " & i _
& " run macro again", vbOKOnly
Exit For
End If
LRow = .Cells(Rows.Count, i).End(xlUp).Row
rngName = Replace(.Cells(1, i), " ", "_")
ActiveWorkbook.Names.Add Name:=rngName, _
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

On Jun 16, 12:27*am, Claus Busch wrote:
Hi Rashid,

try:

Sub myNames()
Dim LCol As Integer
Dim LRow As Long
Dim i As Integer
Dim rngName As String

With ActiveSheet
* * LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
* * For i = 1 To LCol
* * * * If .Cells(1, i) = "" Then
* * * * * * MsgBox "Enter a header in column " & i _
* * * * * * * * & " run macro again", vbOKOnly
* * * * * * * * Exit For
* * * * End If
* * * * LRow = .Cells(Rows.Count, i).End(xlUp).Row
* * * * rngName = Replace(.Cells(1, i), " ", "_")
* * * * ActiveWorkbook.Names.Add Name:=rngName, _
* * * * * * 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

Perfect.
You are a hero.
Thanks a lot
Rashid