Automatically Assign a Named Range Based on a Field

[TeoneSofle]

Hi.

I have 2 fields. Column A is a bunch of values (not important). Column B contains the name of the named ranges I want to assign to the values in column A. For example:

Column A Column B
ABC123 namedRange1
ABC456 namedRange1
ABC789 namedRange2
DEF123 namedRange3
DEF456 namedRange3
DEF789 namedRange3

In the example above, I want values 'ABC123' & 'ABC456' to be in a named range called 'namedRange1'. Now I know how to do this by simply highlighting the values & typing the named range in the name box but seeing as there are hundreds of these, I would like to know if it is possible to automate this via vba or a function?

Regards.
Erik.

[naveenmarapaka]

Hi.
Above is user defined .. like Range 1 you have taken 123 & 456 of ABC but for Range3 you took 3 rows which is 123 , 456 ,789 of DEF.. and only 1 for Range 2.....

do you follow the same pattern for rest of your data ???

[sweep]

Hi,

Does this help?

Sub a()
Dim rCell As Range, rNamedRange As Range
For Each rCell In Range("B1:B6")
If rCell.Value = "namedRange1" Then Set rNamedRange = ModUnion(rNamedRange, rCell.Offset(0, -1))
Next

ThisWorkbook.Names.Add Name:="namedRange1", RefersTo:=rNamedRange


End Sub

Function ModUnion(Rng1 As Range, Rng2 As Range) As Range
    If Rng1 Is Nothing Then
        Set ModUnion = Rng2
    ElseIf Rng2 Is Nothing Then
        Set ModUnion = Rng1
    Else
        Set ModUnion = Application.Union(Rng1, Rng2)
        End If
    End Function

Note the use of the modified Union function - the native function doesn't like having an argument that "Is Nothing"