All our names are given to our heads only!

[prabhafriend]

Is it possible to refer the columns using the value present in the first row (Header Row)?
Like Activesheet.column(“Name”)

[mbarron]

You can assign the column headers as named ranges.

The following will assign each column to a defined name with the Name being taken from the header. The caveats are that the header row must not have any duplicates or contain invalid characters (such as /) and the names cannot be more than 30 characters (I think this is the limit).


[VBA]
sub nameA1()
Range("A1").CurrentRegion.CreateNames Top:=True
end sub[/VBA]

[prabhafriend]

Thanks barron. The Sky is the limit. But Actually it doesn't. Thank you.

[Paul_Hossler]

I like to use somethng like this

[vba]
Option Explicit

Function HeadingToRange(s As String, _
Optional DataOnly As Boolean = True, _
Optional ws As Worksheet = Nothing) As Range

Dim ws1 As Worksheet
Dim iColNum As Long
Dim rData As Range, rLast As Range

On Error GoTo NiceExit

If ws Is Nothing Then
Set ws1 = ActiveSheet
Else
Set ws1 = ws
End If


iColNum = Application.WorksheetFunction.Match(s, ws1.Rows(1), 0)
Set rData = ws1.Columns(iColNum)

If DataOnly Then
Set rLast = rData.Cells(rData.Rows.Count, 1).End(xlUp)
Set rData = rData.Cells(2, 1)
Set HeadingToRange = Range(rData, rLast)
Else
Set HeadingToRange = rData
End If

Exit Function

NiceExit:
Set HeadingToRange = Nothing
End Function


Sub drv()
MsgBox HeadingToRange("BBB").Address
End Sub
[/vba]

Paul