remove duplicates from list, code useful?

[dnickelson]

I've seen a number of posts relating to creating a 'distinct' list of items based off a list that may or may not contain duplicates. What I'm wondering is if the code I'm using would be useful, or whether it would miss some important feature, or if it's inefficient past being usable, etc.

Sub uniquelist()

ss = "Sheet1"       'sheet unfiltered list is located
sr = 1              'row unfiltered list begins
sc = 1              'column unfiltered list is located
srend = Sheets(ss).Cells(65535, sc).End(xlUp).Row

rs = "Sheet1"       'sheet filtered list should be placed in
rr = 1              'row filtered list should start
rc = 5              'column to place filtered list
startr = rr
Sheets(rs).Range(Sheets(rs).Cells(rr, rc), Sheets(rs).Cells(Sheets(rs).Cells(65535, 1).End(xlUp).Row, rc)).ClearContents

    For r = sr To srend
        If Sheets(ss).Cells(r, sc) <> "" Then
            If Application.WorksheetFunction.CountIf(Sheets(rs).Range(Sheets(rs).Cells(startr, rc), Sheets(rs).Cells(rr + 1, rc)), Sheets(ss).Cells(r, sc)) = 0 Then
                Sheets(rs).Cells(rr, rc) = Sheets(ss).Cells(r, sc)
                rr = rr + 1
            End If
        End If
    Next

End Sub

I do know that it's probably frightenly inefficient from a redundant use of sheet and range qualifiers, but haven't gotten around to smoothing that out.

Just curious because I'm looking for a reusable way to perform this task (currently have a replated post suggesting the use of sql syntax, but that might just be wishful thinking).

Thanks,
-Dan

 

[Nimrod]

Dan...

Your method requies a loop for each unique value . In other words a list that contains 200 unique items would cause a looping 200 times.

Excel has a built in function for this that has no looping . Just start your recorder and record the use of the Advanced Filter (eg ... Data ... Filter .. Advanced ... filter ... unique records only ...)

The sol'n below is an example of this ...

STEP ONE : record macro using Advanced filters..

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/27/2005 by nimrod
'

'
Columns("C:C").Select
Columns("A:D").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
"C:C"), CopyToRange:=Range("H1"), Unique:=True
End Sub


STEP 2 .... Replace sheet name and range constants with variables ..


Sub uniquelistV2()
Dim SS, RS As Worksheet
Dim SSrng, TargCell As Range

Set SS = Worksheets("Sheet1") 'sheet unfiltered list is located
Set RS = Worksheets("Sheet2") 'sheet filtered list should be placed in
Set SSrng = SS.Columns("C:C") 'Source Range
Set TargCell = RS.Range("C1")

RS.Cells.ClearContents

SSrng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=SSrng, CopyToRange:=TargCell, Unique:=True

End Sub