RemoveDuplicates not removing all duplicate entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick S. - 12 Mar 2008 18:46 GMT

I can't see what is failing in this macro to remove all duplicates.
I always end up with one duplicate left over?
All help is appreciated.

'======
Sub RemoveDups()
ActiveSheet.Unprotect "2000"
Application.ScreenUpdating = False

Dim Sh3LastRow
Dim Sh3LastCol

With Sheets("Order Specs")
Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).row
Sh3LastCol = Range("A2").Cells(1, .Columns.Count).End(xlToLeft).Column
End With

ActiveSheet.Range("A2:K" & Sh3LastRow).RemoveDuplicates
Columns:=Array(1, 2), Header:=xlYes
Range("A2").Activate
'ActiveSheet.Protect "2000"
Application.ScreenUpdating = True
End Sub
'======

Signature

Regards

VBA.Newb.Confused
XP Pro
Office 2007

Reply to this Message

Joel - 12 Mar 2008 19:35 GMT

It is always due to the cells not containing exactly the same data.
Sometimes it is a space or tab at the beginninhgor end of the data.
sometimes it is a mis-spelled word. Other times it is the letter o instead
of the digit 0. 1's instead of l's. Maybe an extra space between words.
Look and eventualy you find the mis-match.

One way of comparing is copy both cells to notepad. Put each piece of data
on a seperate line and look close at the two lines of data.

> I can't see what is failing in this macro to remove all duplicates.
> I always end up with one duplicate left over?
[quoted text clipped - 20 lines]
> End Sub
> '======

Reply to this Message

Rick S. - 12 Mar 2008 21:32 GMT

The strange thing is, I can copy data from one row into another row, two
times, creating three total copies and the code will only remove one of the
copies?

I just can't figure out why?

Signature

Regards

VBA.Newb.Confused
XP Pro
Office 2007

> It is always due to the cells not containing exactly the same data.
> Sometimes it is a space or tab at the beginninhgor end of the data.
[quoted text clipped - 29 lines]
> > End Sub
> > '======

Reply to this Message

Rick S. - 12 Mar 2008 21:37 GMT

I figured this out!
This was the offending code section: (One string)
ActiveSheet.Range("A1:K" & Sh3LastRow).RemoveDuplicates Columns:=Array(1,
2), Header:=xlYes

'======
Sub RemoveDups()
ActiveSheet.Unprotect "2000"
Application.ScreenUpdating = False

Dim Sh3LastRow
Dim Sh3LastCol

With Sheets("Order Specs")
Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).row
End With

ActiveSheet.Range("A1:K" & Sh3LastRow).RemoveDuplicates
Columns:=Array(1, 2), Header:=xlYes
Range("A2").Activate
'ActiveSheet.Protect "2000"
Application.ScreenUpdating = True
End Sub
'======

Signature

Regards

VBA.Newb.Confused
XP Pro
Office 2007

> I can't see what is failing in this macro to remove all duplicates.
> I always end up with one duplicate left over?
[quoted text clipped - 20 lines]
> End Sub
> '======

Reply to this Message