View Full Version : Finding Duplicates in a Column
simora
03-03-2012, 10:40 PM
I am trying to create a macro that removes duplicates positioned next to each other in a column. For some reason, this macro only removes some of the duplicated. Any ideas why.
See attached sample sheet with accompanying macro.
shrivallabha
03-04-2012, 12:52 AM
I am trying to create a macro that removes duplicates positioned next to each other in a column. For some reason, this macro only removes some of the duplicated. Any ideas why.
See attached sample sheet with accompanying macro.
There are some leading spaces which seem to cause the issue. Try the code below:
Sub ColorDuplicateRows()
Dim r As Range
Application.ScreenUpdating = False
'Reset the colors set earlier
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
.Interior.ColorIndex = xlColorIndexNone
.Font.Bold = False
End With
For Each r In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If Trim(r.Value) = Trim(r.Offset(-1, 0).Value) Then 'We remove leading or trailing spaces by Trim
r.Interior.Color = vbYellow
r.Font.Bold = True
End If
Next r
Application.ScreenUpdating = True
End Sub
mdmackillop
03-04-2012, 03:00 AM
If Duplicates are a recurring issue, try this free utility by BrettDJ (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2123-Eliminating-duplicate-data-with-Duplicate-Master-V2.html)
simora
03-07-2012, 04:06 PM
Shrivallabha
mdmackillop
Appreciate all your help.
Thanks for the heads up.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.