Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Items in a list (2000)

    Why am I getting a duplicate in the attached wbk?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unique Items in a list (2000)

    Filters work with column headings (field names)
    Because you don't include the field name in your ranges, one of the 2s is seen as field name, the other as data.

    1) Include B3 in RawData
    2) Set FirstUniqueItem to D3 instead of D4.
    3) Change the text of D3 to RawData (the field name)
    4) Change the code to

    Sub CreateUniqueList()
    Dim DEST As Range
    Dim SOURCE As Range
    Dim rTemp As Range
    Dim lCount As Long

    Set DEST = Range("FirstUniqueItem") 'a ONE CELL range
    Set SOURCE = Range("RawData").SpecialCells(xlCellTypeConstants)
    Set rTemp = Range("IV1") 'row 1 of any unused column

    'Copy Non-Blanks to temp rng
    SOURCE.Copy rTemp

    'Reset temp range to include all items
    lCount = Application.WorksheetFunction.CountA(Range("IV:IV" ))
    Set rTemp = rTemp.Resize(RowSize:=lCount)

    'Sort items
    rTemp.Sort key1:=rTemp(1), Header:=xlYes

    'filter out duplicates
    rTemp.AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=DEST, Unique:=True
    'Clear the temp range
    rTemp.ClearContents

    Set DEST = Nothing
    Set SOURCE = Nothing
    Set rTemp = Nothing
    End Sub

    You could also adapt the technique from <post:=528,282>post 528,282</post:> to create a list of unique items.

Posting Permissions

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