Hello,
I've been trying to hack together code that will let me do the following:
1) In a certain column find duplicates.
2) For those duplicates check another column for duplicates
3) If found, highlight the rows.
I have searched and found a thread which seemed an almost exact match but now I'm stuck: http://www.excelforum.com/excel-form...lete-rows.html
The attached file should be helpful
tester.xlsx
The code I have found in the thread mentioned got me far, but now I'm stuck. Also I must admit that I do not know what I am doing. That is because the code does not have enough comments too explain what it does.
Sub MG30Jun57()
Dim Rng As Range
Dim Dn As Range
Dim Twn As String
Dim oMax As Integer
Dim Rw As Range
Dim nRng As Range
Dim temp As Range
Dim K
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Application.CountIf(Rng.Offset(, 2), Dn.Offset(, 2)) > 1 Then
If Not .Exists(Dn.Value) Then
.Add Dn.Value, Dn.Offset(, 4)
Else
Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn.Offset(, 5))
End If
End If
Next Dn
For Each K In .keys
oMax = 0
For Each Rw In .Item(K)
oMax = Application.Max(Len(Rw), oMax)
If Len(Rw) = oMax Then Set temp = Rw
Next Rw
For Each Rw In .Item(K)
If Not Rw.Address = temp.Address Then
If .Item(K).Count > 1 Then
If nRng Is Nothing Then
Set nRng = Rw
Else
Set nRng = Union(nRng, Rw)
End If
Else
If nRng Is Nothing Then
Set nRng = Rw
Else
Set nRng = Union(nRng, Rw)
End If
End If
End If
Next Rw
Next K
End With
nRng.Interior.ColorIndex = 7
MsgBox nRng.Address
'nRng.EntireRow.Delete
End Sub
The result of the above code is highlighting a couple (wrong) cells. I know I should get rid of the Union declarations, but I don't know how to make sure the correct rows are highlighted. Also I do not understand how this code exactly finds duplicates in the first column, saves the rows which are dupes, and then checks for duplicates in those rows but in another column.
As far as I understand it this will get all the entries in de B column, then put them in a dictionary array. But then it starts counting (I do not understand why) and after that I just do not have the skills to understand the code... yet
.
Any help would be greatly appreciated!
Bookmarks