PDA

View Full Version : All our names are given to our heads only!



prabhafriend
01-05-2010, 07:43 AM
Is it possible to refer the columns using the value present in the first row (Header Row)?
Like Activesheet.column(“Name”)

mbarron
01-05-2010, 08:08 AM
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).



sub nameA1()
Range("A1").CurrentRegion.CreateNames Top:=True
end sub

prabhafriend
01-05-2010, 08:15 AM
Thanks barron. The Sky is the limit. But Actually it doesn't. Thank you.

Paul_Hossler
01-08-2010, 07:39 AM
I like to use somethng like this


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


Paul