Trying to name ranges over a number of columns

[Obfuscated]

I am trying to assign named ranges from column A to column Y. Each column has a header, and each column varies in # of rows of info. I am trying to dynamically name each column for future reference. My code is:

Dim rngselect As Range
Dim ds As String
Set rngselect = ws.Range("Sections") ' (A1:Y1)= column headers
For Each cell In rngselect
With ws
  Set lastcell = .Cells(.Rows.Count, rngselect.Column).End(xlUp)
  rn = lastcell.Row
End With
ds = Chr$(rngselect.Column + 64) & "2:" & Chr$(rngselect.Column + 64) & rn

ws.Names.Add Name:=cell, RefersTo:=ds, Visible:=True
Next cell

The returns for the first column (A) are:
' cell for "A1" = "HP12"
' ds = "A2:A6"

I keep getting the error msg that says my name is not valid. Can someone tell me my mistake in the code above please?

[snb]

sub M_snb()
   for each it in cells(1).currentregion.columns
      it.specialcells(2).name="klum_" & it.column
   next
End Sub

[Obfuscated]

Nope, still get same error.

[snb]

Not this one (hope your sheet isn't protected ?)

Sub M_snb() 
    For Each it In cells(1).currentregion.columns 
        it.specialcells(2).name="klum_" & it.column 
    Next 
End Sub

[Obfuscated]

Not protected, but still get same naming error.

[Obfuscated]

Got it to work, but I need the first entry in column A to be the range name
Cell "A1" = "HP12"
Cell "B1" = "HP14"
etc...
Those headers are what I want as the range names, not the column number.

[snb]

Use a table in Excel.

[SamT]

Dim ds As String 
Dim nm as String

For Each cel In ws.Range("Sections") 
    nm = Replace(Cel, " ", "_") 'Spaces not allowed in Names
    If ISNumeric(Left(nm, 1) then nm = "_" & nm 'Numeric first characters not allowed

    ds = Range(Cel.Offset(1), Cells(Rows.Count, Cel.Column).End(xlUP)).Address
     
    ws.Names.Add Name:=nm, RefersTo:=ds, Visible:=True 
Next cell

[Obfuscated]

For whatever reason, I get the same error as before for naming convention.
All the variables come back with correct info, but it won't name the ranges.
I put your code in a separate sub and called it... same error.
Tried adding "_" before name, skipped through whole process without naming any area.

[Paul_Hossler]

I usually do something like this with a formula to make named ranges dynamic.

There are rules on valid names

Option Explicit
Sub test()
    Dim iCol As Long, i As Long
    
    For iCol = 1 To ActiveSheet.Cells(1, 1).End(xlToRight).Column
        Call NameAddDynamic(ActiveSheet.Cells(1, iCol))
    Next iCol
    With ActiveWorkbook
        For i = 1 To .Names.Count
            MsgBox .Names(i).Name & " --- " & .Names(i).RefersTo & " --- " & .Names(i).RefersToRange.Address
        Next i
    End With
End Sub

Sub NameAddDynamic(R As Range)
    Dim sFormula As String, sName As String
    Dim rName As Range
    
    Set rName = R.Cells(1, 1)
    
    sName = MakeASCII(rName.Value)
 
    With R.Parent
        sFormula = "=OFFSET("
        sFormula = sFormula & "'" & .Name & "'!" & rName.Address(True, True) & ",0,0,"
        sFormula = sFormula & "COUNTA("
        sFormula = sFormula & "'" & .Name & "'!" & rName.EntireColumn.Address(True, True) & "),1)"
        .Parent.Names.Add Name:=sName, RefersTo:=sFormula
    End With
End Sub
Private Function MakeASCII(S As String) As String
    Dim i As Long
    Dim s1 As String, c As String
    
    c = Left(S, 1)
        
    Select Case c
        Case "0" To "9"
            S = "_" & S
        Case Else
            S = S
    End Select
    
    For i = 1 To Len(S)
        
        c = Mid(S, i, 1)
        
        Select Case c
            Case "a" To "z", "A" To "Z", "0" To "9", "_"
                s1 = s1 & c
            Case " "
                s1 = s1 & "_"
        End Select
    Next i
    MakeASCII = s1
End Function

[snb]

You can't be serious

[SamT]

Are there any Names already in the Sheet?

Dim nm as Long

With ws
For nm = .Names.Count to 1 step -1
.Names(nm).delete
Next

[Paul_Hossler]

@snb --

Actually I was

1. Yours fails if the 'Name to Be' in row 1 will not be a valid name

2. Yours 'hard codes' the RefersTo range so that is data is added or deleted, the named range doesn't reflect the changes


I just prefer my approach.

[p45cal]

Amongst many other reasons for your code to fail is this one:

' cell for "A1" = "HP12"

HP12 is an invalid name because it the same as a cell address; if you were to use it in a formula how would Excel be able to differentiate your Name from the cell HP12 - it would appear exactly the same.
One way is to prefix the header with, say, an underline '_'.
The following code will do the same as your code intends (as long as there is no other reason for the header being an invalid name):

Set ws = ActiveSheet 'had to add this line as there was no evidence as to what ws was.
With ws
  For Each cell In .Range("Sections")
    .Range(cell.Offset(1), .Cells(.Rows.Count, cell.Column).End(xlUp)).Name = "_" & cell
  Next cell
End With

Just be aware (both yours and my code), if the column is empty except for the header, the named range will be the header and the cell below it.

[Paul_Hossler]

@p45cal -- thanks for the reminder. I'll add that to my MakeASCII function

[Obfuscated]

First of all, let me say thanks to everyone for the answers and ideas. P45Cal, you are correct, I was trying to name the range "HP12" and that was a cause of the failed name try. What I finally ended up with is this:

For i = 65 To 89
    ds = Chr$(i) & "1"
    Range(ds).Select
    cel = Range(ds).Value
    If Left(Range(ds), 1) = "H" Then
        cel = "_" & Range(ds).Value
    End If
    Range(Selection.Offset(1, 0), Selection.End(xlDown)).Name = cel
Next I

This seems to do the job nicely. Thanks again guys, keep exceling.