Display unique values in a range.
 

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.

Display unique values in a range.
 

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)

"future" wrote in message
om...
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.

Display unique values in a range.
 

Or you could go to <data<filter<advanced filter select "copy to another
place" and
tick "unique records."

regards
Bill K

"Bob Phillips" wrote in message
...
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)

"future" wrote in message
om...
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.

Display unique values in a range.
 

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?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Display unique values in a range.
 

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

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