How to create named ranges using for loop in Excel?

Question

I'd like to create named ranges using for loop script in Excel.

My aim is to create ranges for each rows from B to D columns, which are named after column A. E.g.: range 'alfae' contains B1:D1, and range 'alfag' contains B2:D2, and so on.

Here's the basic script that I created:

Sub ExampleMacro()
'
' ExampleMacro Macro
'
'
        Range("B1:D1").Select
        ActiveWorkbook.Names.Add Name:="alfae", RefersToR1C1:="=Munka1!R1C2:R1C4"
    End Sub

My question: how can I loop through each row in column A? And how can I name a range with the value of a cell (e.g. A1:D1="alfae"; A2:D2="alfag"; etc.)?

Thank you in advance.

If this is not necessarily to be done with VBA you can use "Create names from selection" (Ctrl + Shift + F3) in the Formula menu to do this for the whole Range. Or see How to Create Named Ranges to Use in Excel Formulas for an advanced mode. — Pᴇʜ
– Pᴇʜ, Commented Nov 28, 2017 at 14:39
@Peh - a bit of offtopic - what were you doing in order to find Ctrl+Shift+F3 as a shortcut for names from selection? — Vityata
– Vityata, Commented Nov 28, 2017 at 14:48
@Vityata Hover the mouse over the menu button in Formula menu of the Ribbon shows a tooltip (Excel 2016) Don't know if this differs in localized Office versions (I used German Office). — Pᴇʜ
– Pᴇʜ, Commented Nov 28, 2017 at 15:05
@Peh - in 2010 it does not show up. But still, it works! (Unfortunately I use German Office as well) — Vityata
– Vityata, Commented Nov 28, 2017 at 15:12

Gary's Student · Accepted Answer · 2017-11-28 15:13:09Z

4

With the proposed names in column A, try:

Sub WhatsInAName()
    For Each r In Columns(1).SpecialCells(2)
        Range(r.Offset(, 1), r.Offset(, 3)).Name = r.Value
    Next r
End Sub

EDIT#1:

To save some typing, you can replace:

Range(r.Offset(, 1), r.Offset(, 3)).Name = r.Value

with:

r.Offset(, 1).Resize(, 3).Name = r.Value

edited Nov 28, 2017 at 15:13

answered Nov 28, 2017 at 14:38

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

4 Comments

Vityata Over a year ago

Using the constant cells through SpecialCells(2) and looping all over is an interesting approach!

Gary's Student Over a year ago

@Vityata It can be useful in cases like this one.

Vityata Over a year ago

Yup, it is - saves some lines for defining the last row and checking for empty values.

Vityata Over a year ago

And I have even started to use it already here :) - stackoverflow.com/a/47535954/5448626

Harassed Dad · Accepted Answer · 2017-11-28 14:37:28Z

2

 sub Example()
 Dim r as range
 set r =  range("A1")
 Do
     ActiveWorkbook.Names.Add Name:=r.text, RefersToR1C1:="=Munka1!R" & r.row & "C2:R" & r.row & "C4"
     set r = r.offset(1.0)
Loop until r = ""
End SUb

answered Nov 28, 2017 at 14:37

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

Comments

J. McConnor · Accepted Answer · 2017-11-28 14:45:00Z

2

Sub MakeNamedRanges()

startrow = 1
endrow = Cells(Rows.Count, "A").End(xlUp).Row

For r = startrow To endrow
    Range(Cells(r, 2), Cells(r, 4)).Select
    rangename = Cells(r, 1)
    ActiveWorkbook.Names.Add Name:=rangename, RefersToR1C1:="=Munka1!R" & r & "C2:R" & r & "C4"
Next r

End Sub

answered Nov 28, 2017 at 14:45

J. McConnor

1011 silver badge11 bronze badges

Comments

Vityata · Accepted Answer · 2017-11-28 14:51:09Z

2

Something like this will be quite enough:

Sub TestMe()

    Dim myRange     As Range
    Dim myRow       As Range

    Set myRange = Range("A1:D4")

    For Each myRow In myRange.Rows
        If Not IsNumeric(Left, Cells(myRow.Row, 1)) Then
          ActiveWorkbook.Names.Add Name:=Cells(myRow.Row, 1), _
          RefersToR1C1:=Range(myRange(myRow.Row, 2), myRange(myRow.Row, myRow.Columns - 1))
        End If
    Next myRow

End Sub

Just make sure that you have some values in A1:A4. You may go further, updating the code. The Left() is used as a condition, because the named range cannot start with a number.

edited Nov 28, 2017 at 14:51

answered Nov 28, 2017 at 14:38

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

MacroMarc · Accepted Answer · 2017-11-28 16:02:20Z

2

You don't really need to loop:

Sub ExampleMacro()
    Dim r As Range
    Set r = Application.Intersect(Sheet4.Range("A1").CurrentRegion, Sheet4.Range("A:D"))
    r.CreateNames Left:=True
End Sub

answered Nov 28, 2017 at 16:02

MacroMarc

3,3242 gold badges13 silver badges22 bronze badges

Comments

jeffreyweir · Accepted Answer · 2017-11-28 21:53:10Z

1

Depending on where you want to call your formulas from, you can probably achieve this using the built in range names you get when you convert your data into an Excel Table (aka ListObject). Use the Ctrl + T keyboard shortcut, or select Insert>Table from the ribbon. Multi-column referencing syntax is like so: =Table3[@[Column2]:[Column4]] ...where [Column2] and [Column4] are the names of your columns.

answered Nov 28, 2017 at 21:53

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to create named ranges using for loop in Excel?

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related