Excel Named Range Create By Column Header Name
Hello All,
I need a vba to do this. Not sure how to do it. I have a range “myRange”
As I go through each cell in myName, I keep creating name range for cells underneath the cel
For example: if cel = c1, then create a named range "c1" from D4 to D5
For example: if cel = c2, then create a named range "c2" from E4 to E5
For each cel in MyName
??
Next cel
Thank you
modelNamedRangeDone---Copy24.xlsm
I need a vba to do this. Not sure how to do it. I have a range “myRange”
As I go through each cell in myName, I keep creating name range for cells underneath the cel
For example: if cel = c1, then create a named range "c1" from D4 to D5
For example: if cel = c2, then create a named range "c2" from E4 to E5
For each cel in MyName
??
Next cel
Thank you
modelNamedRangeDone---Copy24.xlsm
But you could use this with a different range name
Sub asdfsa()
Dim cel As Range
For Each cel In Range("MyName")
ActiveWorkbook.Names.Add "Range_" & cel.Address(False, False), cel.Offset(1).Resize(2)
Next cel
End Sub
This routine will name each column based on the header.
Public Sub AddTableNames( _
ByVal oTable As Range, _
Optional ByVal lHeaderRows As Long = 1, _
Optional ByVal sNamePrefix As String _
)
' Add names for each of the data columns in the table. The column is named
' using all header rows. If a header row cell in the same column is part of a
' merged range then the merged range value is used.
'
' Syntax
'
' AddTableNames(oTable, [lHeaderRows], [sNamePrefix])
'
' oTable - The range containing the table headers and data.
'
' lHeaderRows - The number of header rows. The names used are derived from the
' headers. Optional. If omitted then 1 as assumed.
'
' sNamePrefix - The prefix to use in front of each name. Optional. If omitted
' no prefix ia assumed.
Dim lRow As Long
Dim lColumn As Long
Dim oColumnRange As Range
Dim sRangeName As String
Set oColumnRange = oTable.Columns(1).Resize(o
For lColumn = 1 To oTable.Columns.Count
sRangeName = vbNullString
For lRow = 1 To lHeaderRows
sRangeName = sRangeName & oTable(lRow, lColumn).MergeArea(1, 1)
Next lRow
sRangeName = GetCleanDefinedName(sNameP
AddName sRangeName, oColumnRange.Offset(0, lColumn - 1)
Next lColumn
End Sub
Kevin
Public Sub AddTableNames( _
ByVal oTable As Range, _
Optional ByVal lHeaderRows As Long = 1, _
Optional ByVal sNamePrefix As String _
)
' Add names for each of the data columns in the table. The column is named
' using all header rows. If a header row cell in the same column is part of a
' merged range then the merged range value is used.
'
' Syntax
'
' AddTableNames(oTable, [lHeaderRows], [sNamePrefix])
'
' oTable - The range containing the table headers and data.
'
' lHeaderRows - The number of header rows. The names used are derived from the
' headers. Optional. If omitted then 1 as assumed.
'
' sNamePrefix - The prefix to use in front of each name. Optional. If omitted
' no prefix ia assumed.
Dim lRow As Long
Dim lColumn As Long
Dim oColumnRange As Range
Dim sRangeName As String
Set oColumnRange = oTable.Columns(1).Resize(o
For lColumn = 1 To oTable.Columns.Count
sRangeName = vbNullString
For lRow = 1 To lHeaderRows
sRangeName = sRangeName & oTable(lRow, lColumn).MergeArea(1, 1)
Next lRow
sRangeName = GetCleanDefinedName(sNameP
AddName sRangeName, oColumnRange.Offset(0, lColumn - 1)
Next lColumn
End Sub
Kevin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Thomas,
I checked your code: if renames them as Range_something. Is It possible to use c1,c2,c3,c4 instead like the header row cel values as the named range
Kevin –
For your code: I had issues trying it out. Can you give an example like how you would call it like..
Call AddTableNames (?,?,?)
I checked your code: if renames them as Range_something. Is It possible to use c1,c2,c3,c4 instead like the header row cel values as the named range
Kevin –
For your code: I had issues trying it out. Can you give an example like how you would call it like..
Call AddTableNames (?,?,?)
AddTableNames Sheet1.[D3:E5]
The parameter is the table range including the header row. The other two parameters are optional.
Kevin
The parameter is the table range including the header row. The other two parameters are optional.
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Thomas :)
Great work, yes, in actuality, there would longer string names, so it would work in that case
Thank you all for your help.
Great work, yes, in actuality, there would longer string names, so it would work in that case
Thank you all for your help.
Isn't there built-in functionality for this?
Range("MyName").CreateNames Top:=True, Left:=False, Bottom:=False, Right:=FalseASKER
I am not sure how to get that working
All you would do is run that line of code.
Thomas