Results 1 to 2 of 2

Thread: list all items

  1. #1

    list all items


    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?


    Sub ShowAllItems1()


    Set a = Selection

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

    End Sub
    Attached Images Attached Images
    • File Type: jpg X.jpg (46.7 KB, 2 views)

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Apr 2005

    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
    On Error GoTo 0

    i = 1
    For i = 1 To coll.Count

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

    End Sub
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts