How to create Named Ranges using a loop in Excel VBA?

Question

I would like to create named ranges in my Excel sheet which should be copied to another worksheet later.
I have created a code that names the ranges like this:

Sub Sample1()

Dim RangeName As String

    RangeName1 = Worksheets("DB_Elements").Range("B3")
    ThisWorkbook.Names.Add Name:=RangeName1, RefersTo:=Worksheets("DB_Elements").Range("B3:V14")
    
    RangeName2 = Worksheets("DB_Elements").Range("B17")
    ThisWorkbook.Names.Add Name:=RangeName2, RefersTo:=Worksheets("DB_Elements").Range("B17:V28")

However, this needs to be repeated another 85 times. So I need a loop in my VBA that would create each time a new named range based on the "B" cell value which is always offset 14 rows down. The named ranges always consist of 12 rows and 21 columns.

ZygD · Accepted Answer · 2021-01-05 10:54:30Z

1

Try this:

Sub Sample1()
  Dim i As Long
  Dim j As Long
  With Worksheets("DB_Elements")
    For i = 1 To 85
      j = (i - 1) * 14 + 3
      ThisWorkbook.Names.Add Name:=.Range("B" & j), RefersTo:=.Range("B" & j & ":V" & (j + 11))
    Next
  End With
End Sub

answered Jan 5, 2021 at 10:54

ZygD

24.8k41 gold badges106 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create Named Ranges using a loop in Excel VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related