Del dublicates

[Jomu]

Anyone who could help me.
That macro asks if I want to delete or not. If I choose "not" it leaves current cell red. How can I degree that. If I choose not to delete it should
leave that cell as it was in the begin. Please help me.

Sub oma()
Range("A:A").Interior.ColorIndex = xlNone
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & i), Cells(i, 1).Value) > 1 Then
Cells(i, 1).Interior.ColorIndex = 3
If MsgBox("Duplicates highted...Do you want to delete", vbYesNo) = vbYes Then
Cells(i, 1).EntireRow.Delete
End If
End If
Next i
End Sub

 

[VoG]

Sub oma()
Range("A:A").Interior.ColorIndex = xlNone
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & i), Cells(i, 1).Value) > 1 Then
Cells(i, 1).Interior.ColorIndex = 3
If MsgBox("Duplicates highted...Do you want to delete", vbYesNo) = vbYes Then
Cells(i, 1).EntireRow.interior.colorindex=xlnone
Cells(i, 1).EntireRow.Delete
End If
End If
Next i
End Sub

 

[Jomu]

Hi. Thanks for the answer. However Your code still leaves cell red. That is just I do not want.

 

[brettdj]

hth

Cheers

Dave

Sub oma()
    Range("A:A").Interior.ColorIndex = xlNone
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.CountIf(Range("A1:A" & i), Cells(i, 1).Value) > 1 Then
            Cells(i, 1).Interior.ColorIndex = 3
            If MsgBox("Duplicates highted...Do you want to delete", vbYesNo) = vbYes Then
                Cells(i, 1).EntireRow.Delete
            Else
                 Cells(i, 1).Interior.ColorIndex = xlNone
            End If
        End If
    Next i
End Sub

 

[Jomu]

Thanks Breddtj

Thank You very much. This was just what I want to. However, is there any possibilty to add "cancel" to code, while searching duplicates from the sheet ?
Greetings from Finland.

 

[brettdj]

No probs

Sure, try this update

Sub oma()
Dim lngSel As Long
    Range("A:A").Interior.ColorIndex = xlNone
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.CountIf(Range("A1:A" & i), Cells(i, 1).Value) > 1 Then
            Cells(i, 1).Interior.ColorIndex = 3
            lngSel = MsgBox("Duplicates highted...Do you want to delete", vbYesNoCancel)
            If lngSel = vbCancel Then Exit Sub
            If lngSel = vbYes Then
                Cells(i, 1).EntireRow.Delete
            Else
                 Cells(i, 1).Interior.ColorIndex = xlNone
            End If
        End If
    Next i
End Sub

 

[Jomu]

Thanks again. You are a MAN....

How about, is it possible that code goes throw not just A column but also B and C:s ? Kind regards

No probs

Sure, try this update

Sub oma()
Dim lngSel As Long
    Range("A:A").Interior.ColorIndex = xlNone
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.CountIf(Range("A1:A" & i), Cells(i, 1).Value) > 1 Then
            Cells(i, 1).Interior.ColorIndex = 3
            lngSel = MsgBox("Duplicates highted...Do you want to delete", vbYesNoCancel)
            If lngSel = vbCancel Then Exit Sub
            If lngSel = vbYes Then
                Cells(i, 1).EntireRow.Delete
            Else
                 Cells(i, 1).Interior.ColorIndex = xlNone
            End If
        End If
    Next i
End Sub

 

[Jomu]

Hi. Is it possible that code shows which items are dublicates. I mean that I have made a button where macro exist and when i push that button macro starts. How ever it start from the bottom of the worksheet and goes upward. When the duplicate has been found it gives a message but doesnt show that row where dublicates are found. Buttons are at the top of the worksheet and those rows where buttons are are locked. I can send an example if it helps.