Macro to remove duplicate data

[Isabella]

Hi, i have the following sample data in which there maybe duplicates in each row, i need a code that will remove the duplicates.

So from this

Date hearder Row is J1

<TABLE style="WIDTH: 378pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=502 border=0><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=122 height=17>BHP</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 82pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=109>RIO</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>WOW</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=100> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=85> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BTRT</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">RTY</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">RTY</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LOI</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">RTY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>TEL</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">TEL</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SPA</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>456123</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>456123</TD></TR></TBODY></TABLE>

To this

<TABLE style="WIDTH: 239pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=317 border=0><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=122 height=17>BHP</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 82pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=109>RIO</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>WOW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BTRT</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">RTY</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LOI</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>TEL</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SPA</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">456123</TD></TR></TBODY></TABLE>

 

[VoG]

Try

Sub DelDup()
Dim LR As Long, LC As Integer, i As Long, j As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    LC = Cells(i, Columns.Count).End(xlToLeft).Column
    For j = LC To 2 Step -1
        If WorksheetFunction.CountIf(Rows(i), Cells(i, j)) > 1 Then Cells(i, j).Delete shift:=xlShiftToLeft
    Next j
Next i
End Sub

 

[Isabella]

Awesome, thanks Vog

Try

Sub DelDup()
Dim LR As Long, LC As Integer, i As Long, j As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    LC = Cells(i, Columns.Count).End(xlToLeft).Column
    For j = LC To 2 Step -1
        If WorksheetFunction.CountIf(Rows(i), Cells(i, j)) > 1 Then Cells(i, j).Delete shift:=xlShiftToLeft
    Next j
Next i
End Sub

 

[Snoopy23]

I need something like the macro mentioned in the question below, but I just need to check for duplicate entries in a column (B for instance). I don't know very much about the codes for macros. Can someone help? Thanks a bunch!

 

[VoG]

I need something like the macro mentioned in the question below, but I just need to check for duplicate entries in a column (B for instance). I don't know very much about the codes for macros. Can someone help? Thanks a bunch!

Try

Sub DelDup()
Dim LR As Long, LC As Integer, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If WorksheetFunction.CountIf(Columns("B"), Range("B" & i).Value) > 1 Then Range("B" & i).Delete shift:=xlShiftUp
Next i
End Sub

 

[Snoopy23]

Thanks! That did it. This will save a lot of manual scanning for dupes.

Valerie

 

[Snoopy23]

Need to Tweak your macro to remove duplicate data

Hello VoG. I've discovered I didn't ask the right question.
What I really need is to delete the entire row of duplicate entries in column B. Can that be done?

Thanks again!

 

[VoG]

Try

Sub DelDup()
Dim LR As Long, LC As Integer, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If WorksheetFunction.CountIf(Columns("B"), Range("B" & i).Value) > 1 Then Rows(i).Delete
Next i
End Sub

 

[sarefeen]

Hi Vog,

I tried your below macro but didn't work. My problem is:

A B C
ID Rec Pay
1 M123 100
2 M123 -50
3 M614 -50
4 M614 500
5 M123 100
6 M614 500

I need rows 5 and 6 deleted.

Thanks for you help.

Try

Sub DelDup()
Dim LR As Long, LC As Integer, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If WorksheetFunction.CountIf(Columns("B"), Range("B" & i).Value) > 1 Then Range("B" & i).Delete shift:=xlShiftUp
Next i
End Sub

 

[VoG]

Try

Sub DelDup()
Dim LR As Long, LC As Integer, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Columns("D").Insert
Range("D1:D" & LR).Formula = "=B1&C1"
For i = LR To 2 Step -1
    If WorksheetFunction.CountIf(Columns("D"), Range("D" & i).Value) > 1 Then Rows(i).Delete
Next i
Columns("D").Delete
End Sub