I want to name ranges dynamically

Hi,

In cell B1, type Schadejaar. In cell A1, WBE and so on. Select A1:B15000 and go to Formulas > Create name from selection. Check the box of Top Row. Click on OK.

Hope this helps.

Dear @Frank,

Good day, and I appreciate the detailed description of your issue. I understand that you want to dynamically create named ranges where the name comes from Column A (Item) and the actual range reference comes from Column C (Ranges). One practical way to achieve this is by using VBA. You can try the following steps:

• Press Alt + F11 to open the VBA Editor.
• Go to Insert > Module.
• Paste the code below into the new module.
• Close the editor.
• Press Alt + F8, select CreateDynamicNamedRanges, and click Run.

Sub CreateDynamicNamedRanges()
 
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Dim NameStr As String
    Dim RefStr As String
   
    Set ws = ActiveSheet   ' Or change to ThisWorkbook.Sheets("YourSheetName")
   
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
    Application.ScreenUpdating = False
   
    For i = 2 To LastRow
   
        NameStr = Trim(ws.Cells(i, "A").Value)   ' Column A = Name
        RefStr = Trim(ws.Cells(i, "C").Value)    ' Column C = Range reference
       
        If NameStr <> "" And RefStr <> "" Then
       
            ' Clean the name so it is valid for Excel
            NameStr = Replace(NameStr, " ", "_")
            NameStr = Replace(NameStr, "-", "_")
            NameStr = Replace(NameStr, ".", "_")
           
            ' Delete existing name if it already exists
            On Error Resume Next
            ThisWorkbook.Names(NameStr).Delete
            On Error GoTo 0
           
            ' Create the named range
            ThisWorkbook.Names.Add Name:=NameStr, RefersTo:="=" & RefStr, Visible:=True
           
        End If
       
    Next i
   
    Application.ScreenUpdating = True
   
    MsgBox "Named ranges have been created/updated successfully!", vbInformation
 
End Sub

After running the macro, you should be able to use the created names in formulas, for example:

• =SUM(Bedrag)
• =AVERAGE(Schadejaar)
• And similar formulas using the defined names created from Column A.

A few notes:

• Excel defined names cannot contain spaces. So an item such as: Diersoort Categorieën cannot be used directly as a range name unless the space is replaced, for example: Diersoort_Categorieën
• Column A should contain the names you want to create.
• Column C should contain valid range references.
• If a defined name already exists, the macro will delete and recreate it with the updated reference.
• If you change the range reference in Column C later, you can run the macro again to update the defined names.

To verify the result, go to the Formulas tab > Name Manager and check whether the named ranges were created or updated correctly.

👁 User's image

I hope this information is helpful. Please try the steps above and let me know how it goes. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.