hi
if you want to have just one of duplicate with no duplicate items use this code for a button in sheet 2
Application.ScreenUpdating = False
Dim LastRow, lastrow2 As Long
'Search for any entry, by searching backwards by Rows.
LastRow = Sheet1.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each Cell In Sheet1.Range("A2:F" & LastRow)
Sheet2.Range("A1").Offset(Application.WorksheetFunction.CountA(Sheet2.Range("A1:A1000000")), 0) = Cell.Value
Next Cell
'""""""""""""""""""""""""""""""""""""
lastrow2 = Sheet2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each c In Sheet2.Range("A1:A" & lastrow2)
If WorksheetFunction.CountIf(Sheet2.Range("A1:A" & lastrow2), c.Value) > 1 And c.Value <> "" Then
Rows(c.Row).Delete
End If
Next c
Application.ScreenUpdating = True
if you don't need duplicate , i mean just unique item use this code :
Application.ScreenUpdating = False
Dim LastRow As Long
'Search for any entry, by searching backwards by Rows.
LastRow = Sheet1.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each Cell In Sheet1.Range("A2:F" & LastRow)
Sheet2.Range("A1").Offset(Application.WorksheetFunction.CountA(Sheet2.Range("A1:A1000000")), 0) = Cell.Value
Next Cell
Application.ScreenUpdating = True
Bookmarks