vba to create named range columns

[MikeL]

Hi I want to use VBA to create named range columns for all the US Presidents starting in Col E, Row 5. The names will all be listed across row 4. The row length of the data below is variable ie 100 to 200 rows so maybe use last row?

Thanks in advance,
Mike

 

[Fluff]

How about

Sub MikeL()
    Dim Cl As Range
    For Each Cl In Range("E4", Cells(4, Columns.Count).End(xlToLeft))
        Range(Cl, Cl.End(xlDown)).CreateNames True
    Next Cl
End Sub

 

[MikeL]

Thank you. If several column headers have numbers (ie 0,1,2,3) etc it seems like there is not a named range created. Checking if this can be addressed in the macro?

 

[Fluff]

What values do you have in row 4?

 

[MikeL]

Row 4 has mostly strings of names (used Presidents as example) but also has numeric values. And xltoLeft looks at Col D to start then pretty much uses that range as it loops correct? Reason I ask is Col D may have some null values as compared to col A.

 

[Fluff]

What are the values when it doesn't create a named range?

 

[MikeL]

It doesnt create on the numeric values

 

[Fluff]

How about

Sub MikeL()
    Dim Cl As Range
    For Each Cl In Range("E4", Cells(4, Columns.Count).End(xlToLeft))
        If IsNumeric(Cl) Then
            Range(Cl, Cl.End(xlDown)).Name = "_" & Cl
        Else
            Range(Cl, Cl.End(xlDown)).CreateNames True
        End If
    Next Cl
End Sub