How to create named ranges using for loop in Excel?

Viewed 2144

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 Answers
Related