Remove Duplicates

[fari1]

hi,
i'm working over a code and a part of it includes removing the duplicates out of the source sheet and then put the fileterd results to the destination sheet.
i'm opting to make it a subroutine and will call in the original code that is run in the source sheet.

Thanks

 

[AlphaFrog]

I recently posted some code in another thread to copy unique values from one column to a another sheet. You may find it useful.

http://www.mrexcel.com/forum/showpost.php?p=2834027&postcount=4

 

[fari1]

hi, thank for reply, i want to run this remove duplicate code for the whole sheet not for just one column

 

[Kenneth Hobson]

This should illustrate how to use RemoveDuplicates.

Sub RemoveDuplicates()
  Dim i As Integer
  For i = 1 To 10
    Range("A" & i).Value2 = i
    Range("B" & i).Value2 = i
  Next i
  
  'add a duplicate to row 2.
  Range("A2").Value2 = 1
  Range("B2").Value2 = 1
  
  'Copy usedrange to sheet2 and remove duplicates
  Sheet1.UsedRange.Copy Sheet2.Range("A1")
  Sheet2.UsedRange.RemoveDuplicates Array(1, 2), xlNo
End Sub

 

[fari1]

the code is not working somehow, bringing in some weird stuff

 

[fari1]

and errors as well

 

[Kenneth Hobson]

You must be using Excel 2003? This works for 2010 I know.

 

[SatishKumar.Dx]

Hello,
If you are using 2003 excel then use record and use advance filter then edit you are code as u required, Advance filter will filter all the data.

or

concatenate all the columns into one column and remove duplicate(delete entire row) either by countif formula or Advance filter.

for 2007 excel you can record macro and use a command called "Remove Duplicates" in Data tab in menu bar, use can select all columns if it suppose to delete the duplicates in entire column.

All the best