Automatically Create Named Ranges with Dynamic Names

Question

I want to select a range of cells in a column, automatically create a named range with the selected cells which incorporates a number into the name, and then do the same for a column that is 5 columns over. Here is what I have so far but it doesn't work:

Sub AddNamesMacro()

Dim startingrow As Integer
startingrow = 1

Dim endingrow As Integer
endingrow = 1000

For i = 0 To 100 Step 4
Worksheets("TTMIS").Range(Cells(startingrow, i + 1), Cells(endingrow, i + 1)).Select


ThisWorkbook.Names.Add Name:="IS_AccountNames_Year" & i, RefersTo:=Selection.Address(External:=True)
Next

End Sub

When I run it, then for any i, I do get the named ranges the way I want them; however, in the Name Manager each named range which resulted from the macro has the same thing for the "Value" and the "Refers To" column. For instance the "Refers To" column will say '[Filename]TTMIS'!$A$1:A$1000 and the "Value" Column will say '[Filename]TTMIS'!$A$1:A$1000 instead of listing out the values

Then see this for why Worksheets("TTMIS").Range(Cells(startingrow, i + 1), Cells(endingrow, i + 1) is problematic (unqualified Cells). — BigBen
– BigBen, Commented Mar 22, 2021 at 21:36
=Selection.Address(External:=True) returns a string of the address. You want: = "=" & Selection.Address(External:=True) to create a formula. — Scott Craner
– Scott Craner, Commented Mar 22, 2021 at 21:38

Scott Craner · Accepted Answer · 2021-03-22 21:51:38Z

4

As per all the comments:

Sub AddNamesMacro()

    Dim startingrow As Long
    startingrow = 1
    
    Dim endingrow As Long
    endingrow = 1000
    
    Dim rng As Range
    
    Dim i As Long
    For i = 0 To 100 Step 4
        With Worksheets("TTMIS")
            Set rng = .Range(.Cells(startingrow, i + 1), .Cells(endingrow, i + 1))
        End With
        
        ThisWorkbook.Names.Add Name:="IS_AccountNames_Year" & i, RefersTo:= "=" & rng.Address(1, 1, xlA1, 1)
    Next

End Sub

One more suggestion:

To allow for better sorting in the names manager I would use:

Name:="IS_AccountNames_Year" & Format(i,"000")

To make all the numbers 3 digits.

edited Mar 22, 2021 at 21:51

answered Mar 22, 2021 at 21:43

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Automatically Create Named Ranges with Dynamic Names

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related