Create Named Range From Values in Adjacent Cells

[Mayanwolfe]

Hello,

I am trying to create a series of several named ranges in a single column that are defined by the values in previous columns using VBA. For example, I have one column for "colors" and another for "shades". The third column is their product numbers. I would like to group the product numbers into named ranges that are defined and named based on the concatenation of color & shade.

For example, the first range below would be named BlueLight and contain the four items in the PRODNO column associated with this combination. The next would be named BlueMedium and have only one row, and so on. I have made sure that the columns are ordered by COLOR and SHADE accordingly. Essentially, I need the macro to add cells to the range until the value of COLOR&SHADE changes.

COLOR	SHADE	PRODNO
Blue	Light	9508​
Blue	Light	2595​
Blue	Light	2656​
Blue	Light	6407​
Blue	Medium	1838​
Blue	Dark	3142​
Blue	Dark	3628​
Red	Light	6787​
Red	Medium	3540​
Red	Medium	6103​
Red	Medium	2793​
Red	Medium	4004​
Red	Dark	4745​
Red	Dark	6229​

 

[John_w]

Try this macro, which assumes your data is in columns A:C.

Public Sub Create_Named_Ranges()

    Dim r As Long, lastRow As Long, startRow As Long
    Dim colourShade As String
    
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        r = 2
        While r <= lastRow
            startRow = r
            colourShade = .Cells(r, "A").Value & .Cells(r, "B").Value
            While r <= lastRow And .Cells(r, "A").Value & .Cells(r, "B").Value = colourShade
                r = r + 1
            Wend
            ActiveWorkbook.Names.Add Name:=colourShade, RefersTo:=.Range(.Cells(startRow, "C"), .Cells(r - 1, "C"))
        Wend
    End With
    
End Sub

 

[Mayanwolfe]

That worked perfectly! So compact and clean as well! Thank you!