I'm using the below code to list unique cities from a client list (column L).
I would like to also add additional unique cities that are in a jobsite list (column U).
How do I add the additional data from column U to the Scripting Dictionary before transposing it to the Data sheet?
Code
'Unique Cities
j = Application.Transpose(Range("L6", Range("L" & Rows.Count).End(xlUp)))
With CreateObject("Scripting.Dictionary")
For Each i In j
.Item(i) = i
Next i
Sheets("Data").Range("F2:F" & Sheets("Data").Cells(Rows.Count, "F").End(xlUp).Row).ClearContents
Sheets("Data").Range("F2").Resize(.Count) = Application.Transpose(.Keys)
End With
Sheets("Data").Sort.SortFields.Clear
Sheets("Data").Sort.SortFields.Add Key:=Range("F2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets("Data").Sort
.SetRange Range("F2:F" & Sheets("Data").Cells(Rows.Count, "F").End(xlUp).Row)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Display More