list all items

[clarksonneo]

Hi,

I want a marco that can list all items in a range.
However, I don't want repeated items.

Please refer to the picture below.

Column H is the result that I want.
However, my code can produce the result like column G only.

The items are in range(C5:E7).

Could you please amend my code so that I can produce the result like column H?

Thanks

[VBA]
Sub ShowAllItems1()

ActiveCell.CurrentRegion.Select

Set a = Selection

i = 1
For Each x In a
x.Copy
Cells(4 + i, 7).PasteSpecial
i = i + 1
Next

End Sub
[/VBA]

[Bob Phillips]

[vba]

Sub ShowAllItems1()
Dim x As Range
Dim coll As Collection
Dim i As Long

Set coll = New Collection

On Error Resume Next
For Each x In ActiveCell.CurrentRegion

coll.Add x.Value, x.Value
Next
On Error GoTo 0

i = 1
For i = 1 To coll.Count

Cells(4 + i, 7).Value = coll.Item(i)
Next i

End Sub
[/vba]