At the moment I have a range of names, and I need to create a new column which only contains the unique names.
Sub Unique_Values()
mySheet = Sheets("Sheet1").Range("E9:I20")
With CreateObject("scripting.dictionary")
For Each cell In mySheet
a = .Item(cell)
Next
Range("D2").Value = Join(.keys, vbLf)
End With
End Sub
This code creates a dictionary and returns the list of unique names, but it’s one long list (i’ve just inserted it into D2) but I need it to populate column D with the unique names, one name per cell. I can’t quite figure out how to loop through the keys and put them into an individual cell
>Solution :
If you use a collection you can create a unique list and write to the range. A collection will not let you add the same index key twice, therefore we ignore the error and then resume error checking when done writing.
Sub test()
Dim myNames As New Collection
Dim mySheet As Range
Dim i As Long
Set mySheet = Sheets("Sheet1").Range("E9:I20")
On Error Resume Next
For Each cell In mySheet
myNames.Add cell, cell.Value
Next
On Error GoTo 0
For i = 1 To myNames.Count
Worksheets("Sheet1").Cells(i + 2, 4) = myNames(i)
Next
End Sub