Display unique values in a range.

  • Thread starter Thread starter future
  • Start date Start date
F

future

I am looking for a procedure that will

loop through items in a range, and return the unique items starting in
a target cell.

Range 1
2
2
2
3
4
5
3
4


Range 2
2
3
4
5


such that when I run the macro each item in Range 1 is looked at until
Range 1 =" ". The result being Range 2.
 
Here's a simple macro to do this

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Or you could go to <data><filter><advanced filter> select "copy to another
place" and
tick "unique records."

regards
Bill K
 
I want the copy to range to be on a separate sheet. Is it possible to
change "B" to be a target cell on another sheet?
 
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
 
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)
 
Back
Top