Create Named Range based on value in column

nazirahmza · Nov 23rd 2019

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

Display More

royUK · Nov 23rd 2019

You cannot use a numeric for a Named Range, the name must begin with a letter.

nazirahmza · Nov 23rd 2019

Edited. From 21 to UNIT21.

**StephenR** · Nov 23rd 2019

You have posted this on another forum without providing a link, which is counter to Ozgrid rules.

Please show consideration to people who help here by posting links to all forums where you have asked this question.

nazirahmza · Nov 23rd 2019

Quote from StephenR

You have posted this on another forum without providing a link, which is counter to Ozgrid rules.

Please show consideration to people who help here by posting links to all forums where you have asked this question.

I am so sorry. This is my first time using OzGrid, and I wasnt aware of the rule. Edited the original post, accordingly.

royUK · Nov 23rd 2019

What's wrong with the code that you have shown?

nazirahmza · Nov 23rd 2019

Quote from royUK

What's wrong with the code that you have shown?

As it goes from row_index 1 to 2, it replaces the previous value. I want to 'expand'/add new data into the named range while retaining the previous data (A2:C2).

royUK · Nov 24th 2019

Can you attach an example workbook showing what you want.

nazirahmza · Nov 24th 2019

Update: I already got the answer on how to create named range based on cell value.

Code

Sub naming()
Dim row_index As Long
Dim lastrow As Long: lastrow = 5
Dim celltomap As Range
Dim RangeName As String
Dim nm As Name
Dim a As String
For row_index = 1 To lastrow
RangeName = Sheet2.Range("A2:C6").Cells(row_index, 2).Value
Set celltomap = Sheet2.Range("A2:C6").Cells(row_index, 3)
On Error Resume Next
Set nm = Sheet2.Names("UNIT" & RangeName)
On Error GoTo 0
If nm Is Nothing Then ' first time you hit "UNIT" & RangeName -> set a new name
Sheet2.Names.Add Name:="UNIT" & RangeName, RefersTo:=celltomap
Else ' name already there -> update its 'RefersTo' property
nm.RefersTo = Union(nm.RefersToRange, celltomap)
End If
Set nm = Nothing ' initialize 'nm' for subsequent 'If nm Is Nothing Then' check
Next
End Sub

Display More

However, the value of named range becomes {...}. Right now, I'm figuring out how to add the named range to combobox.

Create Named Range based on value in column

Share

Similar Threads

Create Named Ranges from List

Tags