Error by creating a named range

[HJA14]

Hi all,


I have a file with all data in column A. In addition, I have a number of unique identifiers in column G. I would like to group all the data in column A based on the identifiers in column G.
The goal is to get multiple named ranges where each range consist of the cells that have a value that is equal to the unique identifier in column G. In addition, this unique identifier is also the name of the corresponding range.

I wrote the following macro to fulfill my needs

Sub createRanges()


Dim LastRowAll, LastRowUnique As Integer
Dim x, y
Dim rng As Variant
Dim rng_name As String


Range("I3").Select
Selection.End(xlDown).Select
LastRowUnique = ActiveCell.Row


Range("B2").Select
Selection.End(xlDown).Select
LastRowAll = ActiveCell.Row



For Each x In Range("I3:I" & LastRowUnique)
    For Each y In Range("B2:B" & LastRowAll)
        
        If IsEmpty(rng) And y = x Then
            rng = y.Address(0, 0)
        ElseIf y = x Then
            rng = rng & "," & y.Address(0, 0)
        End If
    Next y
    
    rng_name = "rng_" & x.Value
    ThisWorkbook.Names.Add Name:=rng_name, RefersTo:=rng
    'Range(rng).Name = rng_name
    rng = Empty
Next x


End Sub

This works perfectly until x becomes "20171229". I am not sure why all the other values work, but this one doesn't. There is no other range called "rng_20171229". In addition, if I create the named range manually and call it like this, it also works. Other ranges such "rng_20160715" and "rng_20160722" in the loop earlier works. Please let me know, thank you

 

[Norie]

What's the error message?

 

[HJA14]

What's the error message?

Run-time Error '1004': Application-defined or object-defined error

I included a picture of the other ranges http://i68.tinypic.com/30idoxi.jpg

 

[Norie]

How many cells are in the range you want to name?

Are the ranges you want to name contiguous or made up of several contiguous ranges?

Does this work?

Sub createRanges()
Dim LastRowAll As Long, LastRowUnique As Long
Dim x, y
Dim rng As Range
Dim rng_name As String

    LastRowUnique = Range("I3").End(xlDown).Row

    LastRowAll = Range("B2").End(xlDown).Row

    For Each x In Range("I3:I" & LastRowUnique)
        For Each y In Range("B2:B" & LastRowAll)

            If rng Is Nothing And y = x Then
                Set rng = y
            ElseIf y = x Then
                Set rng = Union(rng, y)
            End If
        Next y

        rng_name = "rng_" & x.Value
        ThisWorkbook.Names.Add Name:=rng_name, RefersTo:=rng.Address
        'Range(rng).Name = rng_name
        Set rng = Nothing
    Next x

End Sub

 

[HJA14]

How many cells are in the range you want to name?

Are the ranges you want to name contiguous or made up of several contiguous ranges?

Does this work?

Sub createRanges()
Dim LastRowAll As Long, LastRowUnique As Long
Dim x, y
Dim rng As Range
Dim rng_name As String

    LastRowUnique = Range("I3").End(xlDown).Row

    LastRowAll = Range("B2").End(xlDown).Row

    For Each x In Range("I3:I" & LastRowUnique)
        For Each y In Range("B2:B" & LastRowAll)

            If rng Is Nothing And y = x Then
                Set rng = y
            ElseIf y = x Then
                Set rng = Union(rng, y)
            End If
        Next y

        rng_name = "rng_" & x.Value
        ThisWorkbook.Names.Add Name:=rng_name, RefersTo:=rng.Address
        'Range(rng).Name = rng_name
        Set rng = Nothing
    Next x

End Sub

It works! Thank you!