Creating named range based on value in column

Question

I want to create a named range based on the value in column B.

For example

I want to create

• range with name: UNIT21, and from the data above the RefersTo should be A2:C4 and A6:C6.
• range with name: UNIT22 for data with 22 in its column B.

1. How do I select the whole row from A:C for respective row?
2. How do I add new data to existing named range instead of replacing the existing value like my code is does.

Sub naming()
    Dim row_index As Long
    Dim lastrow As Long: lastrow = 5
    
    Dim NamedRange As Range
    Dim celltomap As Range
    
    Dim Rng As Range
    
    For row_index = 1 To lastrow
    
        RangeName = Sheet3.Range("A2:C6").Cells(row_index, 2).Value
        Set celltomap = Sheet3.Range("A2:C6").Cells(row_index, 3)
        
        Sheet3.Names.Add Name:="UNIT" & RangeName, RefersTo:=celltomap
     
        MsgBox ("UNIT" & RangeName)
    
    Next row_index
    
End Sub

cross-posted

Answer 1

Run the below code, it will create the range names.

The value of the named ranges will be {...} if the list is unsorted before you run the code.

Sub NameRanges()

    Dim cUnique As Collection
    Dim Rng As Range
    Dim Cell As Range
    Dim sh As Worksheet
    Dim vNum As Variant
    Dim FrstRng As Range
    Dim UnionRng As Range
    Dim c As Range

    Set sh = ThisWorkbook.Sheets("Sheet1")
    With sh
        Set Rng = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        Set cUnique = New Collection

        On Error Resume Next
        For Each Cell In Rng.Cells
            cUnique.Add Cell.Value, CStr(Cell.Value)
        Next Cell
        On Error GoTo 0

        For Each vNum In cUnique
            For Each c In Rng.Cells
                If c = vNum Then
                    If Not UnionRng Is Nothing Then
                        Set UnionRng = Union(UnionRng, c.Offset(, 1))   'adds to the range
                    Else
                        Set UnionRng = c.Offset(, 1)
                    End If
                End If
            Next c

            UnionRng.Name = "Unit" & vNum
            Set UnionRng = Nothing
        Next vNum
    End With

End Sub

There may be a time you want to delete the range names and start over.

Sub delete_RngNames()
    Dim rn As Name
    For Each rn In ActiveWorkbook.Names
        rn.Delete
    Next rn
End Sub

You did not indicate an ActiveX combobox on the worksheet or a UserForm.

This should work for both, just the buttonNames might be different.

Private Sub CommandButton1_Click()
    Dim s As String, rng As Range, c As Range

    s = "Unit" & Me.TextBox1

    Me.ComboBox1.Clear

    For Each c In Range(s).Cells
        Me.ComboBox1.AddItem c
    Next c

End Sub

This code assumes you have entered something in a textbox. Combining "Unit" & the textbox will indicate what range to populate the combobox