want to create a named range based on the value of in column B.
For example (1, 21, great) (5, 21, sew) (6, 21, test) (7, 22, hi) (1, 21, random)
I want to create named range with name: UNIT21, and from the data above the refersto should be A2:C4 and A6:C6. And another named range= UNIT22 for data with 22 in its column B.
Problem now is, I don't know how to select the whole row from A:C for respective row. And how to add new data to existing named range instead of replacing the existing value like what my code is doing right now.
A beginner in Excel VBA and programming.
I've asked this question in another forum: https://stackoverflow.com/q/59006163/12420134.
Code
- 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