Display unique values in a range.
Try this
Sub Unique()
Dim cLastRow As Long
Dim i As Long
Dim j As Long
Dim prevVal
Dim c As Range
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
prevVal = ""
For i = 1 To cLastRow
Set c = Worksheets("Sheet2").Columns("B").Find(Cells(i, "A"))
If c Is Nothing Then
j = j + 1
Worksheets("Sheet2").Cells(j, "B").Value = Cells(i, "A").Value
prevVal = Cells(i, "A").Value
End If
Next i
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"future" wrote in message
om...
this code only looks to see if a value is unique to its previous value.
any way to see if it is unique to all previous values?
Sub Unique()
Dim cLastRow As Long
Dim i As Long
Dim j As Long
Dim prevVal
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
prevVal = ""
For i = 1 To cLastRow
If Cells(i, "A").Value < prevVal Then
j = j + 1
Cells(j, "B").Value = Cells(i, "A").Value
prevVal = Cells(i, "A").Value
End If
Next i
End Sub
|