How to create named ranges using for loop in Excel?

3
votes

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

Example dataset

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.

6
vbaexcel
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ᴇʜ
@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 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ᴇʜ
@Peh - in 2010 it does not show up. But still, it works! (Unfortunately I use German Office as well) - Vityata

6 Answers

4
votes

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
2
votes
 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
2
votes
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
2
votes

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.

2
votes

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
1
votes

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.