PDA

View Full Version : Deleting of duplicate values in worksheet..



rahulpandita
02-23-2011, 11:00 PM
I have two worksheets namely Data sheet and required format sheet. In the data sheet there are some values which are duplicated. i need to remove that values and get the excel file in the required format which will retain only the last record of duplicate values. I am attaching an excel file. Hope to get an early reply.

Bob Phillips
02-24-2011, 01:32 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<<<< change to suit
Dim Lastrow As Long
Dim i As Long
Dim cell As range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = Lastrow To 1 Step -1

If Application.CountIf(.Cells(i, TEST_COLUMN).Resize(Lastrow - i + 1), .Cells(i, TEST_COLUMN).Value2) > 1 Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

rahulpandita
02-24-2011, 07:53 AM
Thanks a lot...:)