Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Sub Create_RangeNames() 'Creates range names based on header row information Dim wbk As Workbook Dim sht As Worksheet Dim rng, rng2 As Range Dim cl As Object Dim c As Long Dim strAddr As Variant Dim strShName, strHdrName, strCol As String Set wbk = ActiveWorkbook For Each sht In wbk.Sheets c = sht.Cells.SpecialCells(xlLastCell).Column Set rng = sht.Range("A1", sht.Range("A1").Offset(0, c)) For Each cl In rng If cl.Value <> "" Then strShName = Replace(sht.Name, " ", "_", 1) strHdrName = Replace(cl.Value, " ", "_", 1) strAddr = Split(cl.Address, "$") strCol = "$" & strAddr(1) & ":$" & strAddr(1) Set rng2 = sht.Range(cl, cl.End(xlDown)) ActiveWorkbook.Names.Add Name:=strShName & strHdrName, _ RefersTo:="=OFFSET('" & sht.Name & "'!" & cl.Address & ",0,0,COUNTA('" & _ sht.Name & "'!" & strCol & "),1)" End If Next cl Next sht End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.