Delete Duplicate rows with same cell value but.....

DaveUK

Board Regular
Joined
Jan 24, 2005
Messages
245
I am using this code to sort my list which works fine:

Code:
Sub RemoveDuplicates()

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With

With workbooks("Book1").Worksheets("Sheet1")

Range("A1:Q" & Range("C65536").End(xlUp).Row).Sort key1:=Range("G1"), order1:=xlAscending, key2:=Range("B1"), header:=xlYes


' ***********************************

' Need Delete Code here !!!

' ***********************************



Range("A2").Select

End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    End With

End Sub

I need some code that will:

1, Check for duplicate cells in column C2 (as there is a header in row 1) and have a range going to the last nonempty cell in column C.

2, Delete duplicate rows BUT keep the last row as the data has been sorted by the end date and the last row containds the newest data.

Please advise.

TIA
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Code:
Dim LC As Long
Dim BR As Long
BR = Range("c65536").End(xlUp).Row
For LC = BR To 2 Step -1
If Application.WorksheetFunction.CountIf(Range("C2:C65536"), Range("C" & LC)) > 1 Then Rows(LC).Delete
Next LC
 

Watch MrExcel Video

Forum statistics

Threads
1,119,134
Messages
5,576,292
Members
412,715
Latest member
amazingscan
Top