Prompt a Msgbox and delete if duplicates are found in excel

Question

I am using following line to remove duplicates in column.

ActiveSheet.Range("A:B").RemoveDuplicates Columns:=Array(1, 1), Header:=xlYes

Problem is, this is done silently and I don't really know when my data has double values.

I need know by using Msgbox that if duplicates are deleted or not. Is it possible using Count to mention number of entries deleted? in simplest code.

Answer 1

• You can count the rows before removing Duplicates
• Then display the msgbox after counting rows again and Subtracting from Previous count

---

Try:

Dim lr As Long
With ActiveSheet

    lr = .Cells(.Rows.Count, 1).End(xlUp).row
    .Range("A:B").RemoveDuplicates Columns:=Array(1, 1), Header:=xlYes

    If Not lr - .Cells(.Rows.Count, 1).End(xlUp).row = 0 Then
        MsgBox lr - .Cells(.Rows.Count, 1).End(xlUp).row & " Rows Deleted"
    End If
End With