rename top row to allow named ranges

[Mashtonsmith]

Hi

Ideally I need something that will rename any cells in the top row into a format conducive for named ranges..

Eg - "Your title here" would become Your_title_here

I then want the contents of the each column to be a named range.

Column A is titled "Member Name" and has names from A2:A422 - within that are random blank cells.

The macro would rename A1 as Member_Name and then create a named range from A1:A422 called "Member_Name"

It would then work its way from Left to right until all column have had the top row names changed and all will have named ranges.

Thanks!

 

[Fluff]

How about

Sub NameRngs()
   Dim i As Long
   Range("1:1").Replace " ", "_", xlPart, , , , False, False
   For i = 1 To Cells(1, Columns.count).End(xlToLeft).Column
      Range(Cells(1, i), Cells(Rows.count, i).End(xlUp)).Name = Cells(1, i).Value
   Next i
End Sub

 

[Mashtonsmith]

Seems to work ok - until it doesn't like one of the column names. And then I cant find which one it fails on!

 

[Fluff]

When it fails click "Debug" & hover the mouse over

Cells(1, i).Value

What does it say?