Create Name Ranges Using VBA

If you want to create name ranges using vba. Snapshot below-

Here is the code-

' To Create a single name range
Sub create_single_name_range()
Dim rng As Range
' change the range here
Set rng = Sheets(1).Range(" c2:c235")
' create name range with "Jan"
ActiveWorkbook.Names.Add Name:="Jan", RefersTo:=rng

End Sub


'to create mulitple name ranges using for loop
' this macro will create name ranges with month or cell values in row 1
Sub Creating_Name_ranges()
Dim rng As Range
Dim I As Integer
For I = 3 To 8
' Sheets(1).Cells(2, I).Address will give first c2, d2, e2, f2 ,etc
'Sheets(1).Cells(Range("a65356").End(xlUp).Row, I).Address will give c235, d235,e235, etc
Set rng = Sheets(1).Range(Sheets(1).Cells(2, I).Address & ":" & Sheets(1).Cells(Range("a65356").End(xlUp).Row, I).Address)
' create name range with name value of cell c1, d1,e1, etc.
ActiveWorkbook.Names.Add Name:=Sheets(1).Cells(1, I).Value, RefersTo:=rng
Next
End Sub


Download Excel Workbook