Dynamically create named ranges

[BuJay]

Can anyone offer some VBA to dynamically create named ranges with the following examples? I am creating dynamic charts so I need to use named ranges.....but there are a thousand or more names ranges that I need to create....

For example, let's say I need the following - is there a way to create a VBA code that defines the names as described below with the References below?

Range Name	Reference
Actual_201901	=OFFSET(Losses!$TY$77,0,1,1,COUNT(Losses!$TZ$77:$XB$77))
Actual_201902	=OFFSET(Losses!$TY$78,0,1,1,COUNT(Losses!$TZ$78:$XB$78))
Actual_201903	=OFFSET(Losses!$TY$79,0,1,1,COUNT(Losses!$TZ$79:$XB$79))
…	…
Actual_201912	=OFFSET(Losses!$TY$80,0,1,1,COUNT(Losses!$TZ$80:$XB$80))
Actual_202001	=OFFSET(Losses!$TY$81,0,1,1,COUNT(Losses!$TZ$81:$XB$81))
Actual_202002	=OFFSET(Losses!$TY$82,0,1,1,COUNT(Losses!$TZ$82:$XB$82))

 

[shaowu459]

Why do you need to define so many names? How will you use the names in dynamic charts?

 

[BuJay]

If I define a name and use the offset functionality, I can have charts update automatically as new data for each individual series becomes available....the offsets above make the actual name references dynamic - so this part is complete. My challenge is I want to avoid having to manually create each named range individually. It is a repeated task, so I assume there must be a way to iteratively do it on VBA more efficiently.

 

[shaowu459]

How about just use VBA to change the source of the chart? I mean use VBA to find which is the last column and expand the source if needed. For example, original source of the chart is A1:F7, when you add something in row 5, the last column become G:G, use VBA to update the chart source to A1:G7.

 

[BuJay]

appreciate the suggestion, but I am really looking for an approach to handle what I described specifically...
Thanks!

 

[BuJay]

I ended up going with a brute force approach....

Sub Create_Names2018()

ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_60M_0_to_100_N", RefersToR1C1:="=OFFSET(Losses!R11C364,0,1,1,COUNT(Losses!R11C365:R11C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_60M_0_to_100_Y", RefersToR1C1:="=OFFSET(Losses!R12C364,0,1,1,COUNT(Losses!R12C365:R12C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_60M_101_to_120_N", RefersToR1C1:="=OFFSET(Losses!R13C364,0,1,1,COUNT(Losses!R13C365:R13C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_60M_101_to_120_Y", RefersToR1C1:="=OFFSET(Losses!R14C364,0,1,1,COUNT(Losses!R14C365:R14C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_60M_121Plus_N", RefersToR1C1:="=OFFSET(Losses!R15C364,0,1,1,COUNT(Losses!R15C365:R15C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_60M_121Plus_Y", RefersToR1C1:="=OFFSET(Losses!R16C364,0,1,1,COUNT(Losses!R16C365:R16C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_72M_0_to_100_N", RefersToR1C1:="=OFFSET(Losses!R17C364,0,1,1,COUNT(Losses!R17C365:R17C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_72M_0_to_100_Y", RefersToR1C1:="=OFFSET(Losses!R18C364,0,1,1,COUNT(Losses!R18C365:R18C445))"
ActiveWorkbook.Names.Add Name:="Actual_NCO_201807_A_72M_101_to_120_N", RefersToR1C1:="=OFFSET(Losses!R19C364,0,1,1,COUNT(Losses!R19C365:R19C445))"

etc....

I used Excel to efficiently create the strings and then pasted them all into a sub...