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
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