Consulting

Results 1 to 2 of 2

Thread: list all items

  1. #1

    list all items

    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]
    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
    Joined
    Apr 2005
    Posts
    25,232
    Location
    [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]
    ____________________________________________
    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
  •