I have a worksheet with 3 columns and there are duplicate records under
all 3 columns. Here's a sample of data


Col A Col B Col C
1| John Red 15 <--- dup with 3rd row
2| John Blue 18
3| John Red 15 <--- dup with 1st row
4| John Green 20
5| Smith Blue 20 <--- dup with 7th row
6| Smith Red 15
7| Smith Blue 20 <--- dup with 5th row

I am trying write a module of VBA code so that when the user clicks a
button it will find all the unique "combination", and display the
following on a separate worksheet.

John Red 15
John Blue 18
John Green 20
Smith Blue 20
Smith Red 15
Smith Blue 20

I wish I don't have to use advanced filter manually because the
worksheet holds 5000+ records.

Thanks in advance for all the help and ideas.

Andrew



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I wish I don't have to use advanced filter manually because the
worksheet holds 5000+ records.

You can do it with VBA like this


Sub testing()
Sheets("sheet1").Range("A:C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), _
CriteriaRange:="", Unique:=True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Disco" wrote in message ...
I have a worksheet with 3 columns and there are duplicate records under
all 3 columns. Here's a sample of data


Col A Col B Col C
1| John Red 15 <--- dup with 3rd row
2| John Blue 18
3| John Red 15 <--- dup with 1st row
4| John Green 20
5| Smith Blue 20 <--- dup with 7th row
6| Smith Red 15
7| Smith Blue 20 <--- dup with 5th row

I am trying write a module of VBA code so that when the user clicks a
button it will find all the unique "combination", and display the
following on a separate worksheet.

John Red 15
John Blue 18
John Green 20
Smith Blue 20
Smith Red 15
Smith Blue 20

I wish I don't have to use advanced filter manually because the
worksheet holds 5000+ records.

Thanks in advance for all the help and ideas.

Andrew



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/