Microsoft Office Forums - View Single Post - [Solved] HOW TO write a macro for dynamic relative named ranges in Excel?

NoSparks · 01-25-2022, 12:01 PM

Here's a macro that will build that formula and add the named range

Code:

 Sub Create_Dynamic_Range()

    Dim RngName As String, ShtName As String, StartAddress As String
    Dim RngCol As String, ReferString As String
    
    RngName = ActiveCell.Value
    ShtName = ActiveCell.Parent.Name & "!"
    RngCol = "$" & Split(Mid(ActiveCell.Address, 2), "$")(0)
    StartAddress = ActiveCell.Offset(1).Address
    ReferString = "=" & ShtName & StartAddress & ":INDEX(" & ShtName & RngCol & ":" & _
                      RngCol & ",COUNTA(" & ShtName & RngCol & ":" & RngCol & "))"
    ActiveWorkbook.Names.Add Name:=RngName, RefersTo:=ReferString

End Sub