HOW TO write a macro for dynamic relative named ranges in Excel?

NoSparks · #5 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Use of named ranges based on cell content	Intruder	Excel	12	02-25-2019 09:42 AM
Named Ranges Help	SavGDK	Excel	5	05-01-2017 09:41 AM
How to use named ranges in excel vba?	bosve73	Excel Programming	4	01-25-2012 09:26 AM
Dynamic Named Ranges using text	hannu	Excel	0	06-22-2010 04:42 PM
Can't import home adresses in outlook 2010 from excel named ranges	eekie	Outlook	0	05-14-2010 02:04 PM