Sub Create_RangeNames1()
'Creates dynamic named ranges based on header row information only Selection
Dim wbk As Workbook
Dim sht As Worksheet
Dim rng, rng2 As Range
Dim cl As Range 'Object
Dim c As Long
Dim strAddr As Variant
Dim strShName, strHdrName, strCol As String
Set wbk = ActiveWorkbook
Set sht = ActiveSheet
Set rng = Selection
For c = 1 To rng.Columns.Count
If Cells(1, c).Value <> "" Then
strShName = Replace(sht.Name, " ", "_", 1)
strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
strAddr = Split(Cells(1, c).Address, "$")
strCol = "$" & strAddr(1) & ":$" & strAddr(1)
Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
wbk.Names.Add Name:=strShName & strHdrName, _
RefersTo:="=OFFSET('" & sht.Name & "'!" & Cells(1, c).Address & ",0,0,COUNTA('" & _
sht.Name & "'!" & strCol & "),1)"
End If
Next
End Sub
EE.xlsb
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE