Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RemoveDuplicates not removing all duplicate entries
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 '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RemoveDuplicates not removing all duplicate entries
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. "Rick S." wrote: 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 '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RemoveDuplicates not removing all duplicate entries
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? -- Regards VBA.Newb.Confused XP Pro Office 2007 "Joel" wrote: 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. "Rick S." wrote: 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 '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RemoveDuplicates not removing all duplicate entries
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 '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick S." wrote: 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 '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing duplicate entries | New Users to Excel | |||
Removing duplicate entries | Excel Discussion (Misc queries) | |||
removing duplicate entries | Excel Discussion (Misc queries) | |||
Removing duplicate entries. | Excel Discussion (Misc queries) | |||
Removing duplicate entries | Excel Programming |