VBA copying unique and distinct values Alphabetically

[zubin]

Hi All (My Gurus)
I have a code that separates Unique and distinct text values from a range("C25:C3000") and copies the new range to Column II2 of my worksheet....
The code works fine.......only change I would like is that the copied range should be in Alphabetic order ie A to Z
Please help

Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("C25:C" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
Next i
Range("II2").Resize(d.Count) = Application.Transpose(d.keys)

 

[Marc L]

Hi,​

​

so you must add some Range.Sort codeline after your last codeline or use an ArrayList - or SortedList - rather than a Dictionary …​

 

[Marc L]

An ArrayList beginner starter :​

    With CreateObject("System.Collections.ArrayList")
        For Each V In Range("C25", Cells(Rows.Count, 3).End(xlUp)).Value2
            If Not .Contains(V) Then .Add V
        Next
           .Sort
            [II2].Resize(.Count).Value2 = Application.Transpose(.ToArray)
           .Clear
    End With

 

[zubin]

An ArrayList beginner starter :​

    With CreateObject("System.Collections.ArrayList")
        For Each V In Range("C25", Cells(Rows.Count, 3).End(xlUp)).Value2
            If Not .Contains(V) Then .Add V
        Next
           .Sort
            [II2].Resize(.Count).Value2 = Application.Transpose(.ToArray)
           .Clear
    End With

Sorry But...where do i put this code......Is it a replacement of the code i gave??
Will the copied range be in alphabetic order ?
Getting error on this code line

 For Each V In Range("C25", Cells(Rows.Count, 3).End(xlUp)).Value2

Please let me know in detail......

 

[zubin]

Ok Marc.....thanks a lot ...got it working.....

Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("C25:C" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
  Next i
Range("II2").Resize(d.Count) = Application.Transpose(d.keys)

Dim V As Variant
With CreateObject("System.Collections.ArrayList")
        For Each V In Range("C25", Cells(Rows.Count, 3).End(xlUp)).Value2
            If Not .Contains(V) Then .Add V
        Next
           .Sort
            [II2].Resize(.Count).Value2 = Application.Transpose(.ToArray)
           .Clear
    End With

Is it right ? Its working for me
Thanks for your attention.....

 

[Marc L]

​

As the Dictionary is useless so remove all before the Dim V codeline.​

​

You must notice it can be achieved directly with only Excel basics without the need of any external object​

like any Excel beginner operating manually and with the help of the Macro Recorder that's easy …​

 

[zubin]

Ok Marc.....thanks a lot ...got it working.....

Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("C25:C" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
  Next i
Range("II2").Resize(d.Count) = Application.Transpose(d.keys)

Dim V As Variant
With CreateObject("System.Collections.ArrayList")
        For Each V In Range("C25", Cells(Rows.Count, 3).End(xlUp)).Value2
            If Not .Contains(V) Then .Add V
        Next
           .Sort
            [II2].Resize(.Count).Value2 = Application.Transpose(.ToArray)
           .Clear
    End With

Is it right ? Its working for me
Thanks for your attention.....

Sorry Marc...one observation, when the list in Range("C25:C3000") is refreshed ....ie either the range is deleted or new values put in, the copied range in II2 stays and is not refreshed or (deleted)

 

[Marc L]

​

As it was not explicit in the initial post - not the subject of this thread ! - I just can say it must be in an event of the worksheet module.​

​

For further help if necessary, create a new thread but with a complete code and the same for the explanation with nothin' to guess …​

 

[zubin]

​

As it was not explicit in the initial post - not the subject of this thread ! - I just can say it must be in an event of the worksheet module.​

​

For further help if necessary, create a new thread but with a complete code and the same for the explanation with nothin' to guess …​

with a little guess work i worked my way around this shortcoming.......
Added this line just before your code

Sheet1.Range("II2:II3000").ClearContents

Working fine.....Anyways Thanks a lot Marc