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.

Mikku · Accepted Answer · 2019-08-15T08:09:20.793

4

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

edited Aug 15 '19 at 08:09

answered Aug 15 '19 at 08:03

Mikku

6,538
3
15
38

I don't need Msgbox when there is no entry deleted. can you counter that? – VBAbyMBA Aug 15 '19 at 08:07
yes thanks! but I need to use it for day to make sure that I don't need anything else out of it. – VBAbyMBA Aug 15 '19 at 10:00

Prompt a Msgbox and delete if duplicates are found in excel

1 Answers1