Macro to Remove duplicates and display a message with the particular row number is deleted

[Siri Jasthi]

Hi ,
I have four rows as mentioned below .

Column1 Column2 Column3 Column4 Column5
Apple Bat Cat Dog Egg
Ant Bag Can Den Eat
Apple Bat Cat Dog Egg
Air Big Car Dun Eagle

In the above i m having Row 3 same as Row 1 then macro should delete Row 3 and display a message that Row 3 is deleted.Please help me to write macro .

 

[ttray33y]

Hi ,
I have four rows as mentioned below .

Column1 Column2 Column3 Column4 Column5
Apple Bat Cat Dog Egg
Ant Bag Can Den Eat
Apple Bat Cat Dog Egg
Air Big Car Dun Eagle

In the above i m having Row 3 same as Row 1 then macro should delete Row 3 and display a message that Row 3 is deleted.Please help me to write macro .

Private Sub testY()


Dim dict As Object
Dim rowCount As Long
Dim strVal As String


Set dict = CreateObject("Scripting.Dictionary")


rowCount = Sheet1.Range("A1").CurrentRegion.Rows.Count


Do While rowCount > 1


With Sheet1
  strVal = .Cells(rowCount, 1).Value2 & .Cells(rowCount, 2).Value2 & .Cells(rowCount, 3).Value2 & .Cells(rowCount, 4).Value2 & .Cells(rowCount, 5).Value2


  If dict.exists(strVal) Then
    .Rows(rowCount).Interior.ColorIndex = 3
        MsgBox "You are about to delete Row " & rowCount, vbOKOnly
            .Rows(rowCount).EntireRow.Delete
                .Rows(rowCount - 1).Interior.ColorIndex = Null
  Else
    dict.Add strVal, 0
  End If
  rowCount = rowCount - 1
End With
Loop


Set dict = Nothing


Application.ScreenUpdating = True


End Sub