Trying to name ranges over a number of columns

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:

Code:

---

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?

Code:

---

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

---

Nope, still get same error.

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

Code:

---

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

---

Not protected, but still get same naming error.

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.

Use a table in Excel.

Code:

---

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

---

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.

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

There are rules on valid names

Code:

---

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

---

You can't be serious

Are there any Names already in the Sheet?

Code:

---

Dim nm as Long

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

---

@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.

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

Quote:

---

' 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):

Code:

---

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.

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

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:

Code:

---

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.