excel vba: adding distinct values in a range to a new range?
Dim dic As Dictionary Set dic = New Dictionary Dim srcRng As Range Dim lastRow As Integer Dim ws As Worksheet Set ws = Sheets("Sheet1") lastRow = ws.Cells(1, 1).End(xlDown).Row Set srcRng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 1)) Dim cell As Range For Each cell In srcRng If Not dic.Exists(cell.Value) Then dic.Add cell.Value, cell.Value 'key, value End If Next cell Set ws = Sheets("Sheet2") Dim destRow As Integer destRow = 1 Dim entry As Variant 'the Transpose function is essential otherwise the first key is repeated in the vertically oriented range ws.Range(ws.Cells(destRow, 1), ws.Cells(dic.Count, 1)) = Application.Transpose(dic.Items)
I really like the dictionary object in VBA. It's not natively available but it's very capable. You need to add a reference to Microsoft Scripting Runtime then you can do something like this:
You can transpose the array of dictionary keys to the range in one step using dic.keys. Try it out :)