Excel VBA - Naming Range - Has Duplicates. Consider only Unique Values and Name that Range

[Vinod Krishna.C]

Dear all,

I'm facing an issue in Naming a Range.

Sample Data:
Grades

1
1
2
2
3
3

I would like to get the above values as:
1
2
3

Name the Range of Values (1,2,3) as "Grades".

Please see the below code which will print the unique values(here, grades) in column D.

Option Explicit
Sub GetUniqueGrades()

    Sheets("Grades").Select
    Range("A1").Select

    Dim d As Object
    Dim c As Variant
    Dim i, lr As Long
    
    Set d = CreateObject("Scripting.Dictionary")
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
    c = Range("A2:A" & lr)
    
    For i = 1 To UBound(c, 1)
        d(c(i, 1)) = 1
    Next i

    Range("D2").Resize(d.Count) = Application.Transpose(d.keys)
    
End Sub

PFA for reference.

I would like these unique values to be considered as a Range of Values and also Name that Range.

Note: Code to do the needful without Printing the unique values in a separate column.

Please let me know if this would be possible

Thanks,
Vinod Krishna

[TMS]

Some options. The first is your code with the Named Range added; the others are variations. The second avoids cell selection and tidies up a bit. And the third one uses an Advanced Filter rather than using a Dictionary and looping through the cells.

Option Explicit
Sub GetUniqueGrades_TMS1()

    Sheets("GradesNames").Select
    Range("A1").Select

    Dim d As Object
    Dim c As Variant
    Dim i, lr As Long
    
    Set d = CreateObject("Scripting.Dictionary")
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
    c = Range("A2:A" & lr)
    
    For i = 1 To UBound(c, 1)
        d(c(i, 1)) = 1
    Next i

    ' put the named range in column D ...
    With Range("D2").Resize(d.Count)
        .Value = Application.Transpose(d.keys)
        .Name = "nrGrades1"
    End With
    
End Sub

Sub GetUniqueGrades_TMS2()

Dim d As Object
Dim c As Variant
Dim i, lr As Long

Set d = CreateObject("Scripting.Dictionary")

With Sheets("GradesNames")
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    c = .Range("A2:A" & lr)
    For i = 1 To UBound(c, 1)
        d(c(i, 1)) = 1
    Next i
    ' put the named range in column E ...
    With .Range("E2").Resize(d.Count)
        .Value = Application.Transpose(d.keys)
        .Name = "nrGrades2"
    End With
End With
Set d = Nothing
Erase c
End Sub

Sub GetUniqueGrades_TMS3()
Dim lr As Long
With Sheets("GradesNames")
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' put the named range in column F ...
    .Range("A1:A" & lr).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Cells(1, 6), _
        Unique:=True
    lr = .Cells(.Rows.Count, 6).End(xlUp).Row
    .Range(.Cells(2, 6), .Cells(lr, 6)).Name = "nrGrades3"
End With
End Sub

Regards, TMS

[TMS]

Thanks for the rep


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Vinod Krishna.C]

Dear TMS,

Well, I asked you one solution and you gave me three You simply exceed ones expectations

One small request:
How about modifying the above code in such a way that you don't print it in any of the columns?

Idea for possible Solution:
* Consider the Range of Values from A2 to Last Cell (Row wise).
* Store all the unique values into a Array, say UniqueGrades.
* Consider that Array as a Range
* Name that Range as "GradesList"

Please let me know if this would be possible

Regards,
Vinod Krishna

[TMS]

Hi Vinod.

Yes, I tried that but I couldn't get it to work properly. Well, I could create a Named Range that refers to an array and the array contains the grades, but I couldn't use it in Data Validation as I could with the others.

See the attached updated workbook. See if you can get it to do what you want.

Regards, TMS

[Vinod Krishna.C]

Dear TMS,

Thank you for trying your level best.

I guess its one of the drawbacks of Data Validation.

I will continue to use the above logic.

Will update on this post if I get to know of any Alternatives.

Please let us all know if you get to know of any Alternatives.

Thanks,
Vinod Krishna

[TMS]

I think the short, but sad, answer is that it cannot be done.