Delete Row if cell above is the same

[ZaneCOYS]

Hi, I am trying to delete any duplicate row based on column "C" data.

I have sorted the data so the data is sorted by "C" the account number and then "E" the date with the eldest first.

When I run this code it does not pick up all the duplicates and does delete some that are not duplicates.

Worksheets("Raw Data wo ACT").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrow3 = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2", Cells(LastRow, 17)).Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending _
, Key2:=Range("C1"), Order2:=xlAscending _
Count = 1
For x = Lastrow3 To 2 Step -1
If Cells(Lastrow3, 3).Value = Cells(Lastrow3 - 1, 3).Value Then
Range(Cells(Lastrow3, 3)).EntireRow.Delete
Count = Count + 1
End If
Next x

The sheets has about 7000 rows.

 

[VoG]

I think it should be

If Cells(x, 3).Value = Cells(x - 1, 3).Value Then

and you don't need the Count variable.

 

[ZaneCOYS]

Thank you so much, I have been trying to get that to work for hours!

 

[VoG]

Oops - the next line needs changing as well

If Cells(x, 3).Value = Cells(x - 1, 3).Value Then
    Range(Cells(x, 3)).EntireRow.Delete

 

[ZaneCOYS]

Sorry, now I get a global error on the line "Range(Cells(x, 3)).EntireRow.Delete"


' Delete Duplicates
Worksheets("Raw Data wo ACT").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2", Cells(LastRow, 17)).Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending _
, Key2:=Range("C1"), Order2:=xlAscending _
Count = 1
For x = LastRow To 2 Step -1
If Cells(x, 3).Value = Cells(x - 1, 3).Value Then
Range(Cells(x, 3)).EntireRow.Delete
End If
Next x

 

[VoG]

I think I'm having a bad day...

If Cells(x, 3).Value = Cells(x - 1, 3).Value Then
    Cells(x, 3).EntireRow.Delete

 

[ZaneCOYS]

works well thanks. Think I might soon have another question.

 

[Wacolanche]

Although I've coded stuff in ANSI C and Java, this is my first shot at VBA.
If you don't mind indulging a pseudo-noob, I could use some advice on implementing my changes to ZaneCOYS code. I have a need very similar to ZanCOYS. I must delete duplicates from over 5000 rows based on column "A" data, keeping only the rows with unique column "A" data and highest column "B" data. My data must be sorted by A then B, and B should be sorted descending. I made what I hope are the appropriate changes to the code:

Worksheets("Sortie landing status").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrow3 = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2", Cells(LastRow, 2)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending _
, Key2:=Range("B1"), Order2:=xlDescending _

For x = Lastrow3 To 2 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Then
Cells(x, 3).EntireRow.Delete
End If
Next x

Will this do what I think it does?

 

[Wacolanche]

Nevermind. Figured out VBE and got it to run on first try, no problems. Thanks to ZaneCOYS for addressing the problem and VoG for helping to get a workable solution.