VBA to Create Dynamic Named Ranges

Question

I am trying to create named ranges of cells named after the top left cell in each range. This shows what I mean:

Here, I want to have 4 named ranges (Names A, Names B, Names C, Names D) so I can reference them. Is there a way to have excel look for any cell in Column B that starts with "Names" and have it cut off the range when it hits the next cell that starts with "Names"? I apologize if this is not possible, I am still new to excel and not sure if this is doable. Any help is appreciated!

RJ Kelly · Accepted Answer · 2017-12-12 20:36:10Z

3

This is a quick thrown together script to provide the idea of how to approach this. By no means is it pretty, but does show the steps. This can be a lot more complexly written, ie to work with all cells, not start after the first instance of names (See Range("B3") for start, and Range("B4") for starting the looping of cells)

Sub DefineRanges()
    Dim rngStart As Range
    Set rngStart = Range("B3")
    Dim NamedRangeCount As Integer
    NamedRangeCount = 0
    Dim NameRangeName As String
    Dim LastRow As Integer
    For Each cell In Range("B4:B18")
        If LCase(Left(cell.Value, 5)) = "names" Then
            NameRangeName = "Name_" & NamedRangeCount
            ActiveWorkbook.Names.Add Name:=NameRangeName, RefersToLocal:=Range(rngStart.Address & ":D" & cell.Row - 1)
            Set rngStart = Range("B" & cell.Row)
            NamedRangeCount = NamedRangeCount + 1
        End If
        LastRow = cell.Row
    Next
    NameRangeName = "Name_" & NamedRangeCount
    ActiveWorkbook.Names.Add Name:=NameRangeName, RefersToLocal:=Range(rngStart.Address & ":D" & LastRow)
End Sub

answered Dec 12, 2017 at 20:36

RJ Kelly

1991 silver badge7 bronze badges

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

1 Comment

user3047983 Over a year ago

That worked perfectly as I wanted it to! Thank you for making it simple, I can easily follow what you did and understand it better now. I appreciate your help very much.

Collectives™ on Stack Overflow

VBA to Create Dynamic Named Ranges

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related