MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying Unique items from a range, with VBA


Posted by DCP on February 02, 2002 7:41 PM

How do I copy Unique items from a range in one Worksheet and copy to a different sheet, using VBA


Posted by Ivan F Moala on February 02, 2002 11:40 PM

Heres ONE way to do it
assumes;
1) Your data is in Sheet1 @ A2
2) copies to sheet2 @ A2

Sub Copyto()
Range(Range("A1"), Range("A2").End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.copy Destination:=Sheets("Sheet2").Range("A2")
ActiveSheet.ShowAllData
Range("A2").Select
End Sub

Post if you have diff criteria eg paste to
dynamic row ....


Ivan

Posted by Tom urtis on February 02, 2002 11:46 PM

One of many possible ways:

Sub UniqueCopy()
Dim fRange As Range
Set fRange = Range("A1", Range("A65536").End(xlUp))
fRange.AdvancedFilter Action:=xlFilterInPlace, unique:=True
fRange.EntireRow.Cells.SpecialCells(xlCellTypeVisible).Copy _
Sheet2.[A1]
ActiveSheet.ShowAllData
End Sub

Tom Urtis

Posted by Tom Urtis on February 02, 2002 11:52 PM

Sorry Ivan, didn't see your's first (nt)

Posted by Ivan F Moala on February 03, 2002 1:34 AM

Tom - No problem (NT)