How to Delete first Duplicate values?

[sreekhosh]

Here "Raj" and "Sree" are duplicate value. I need to remove the No.1,2,3 and should keep No.5 and 6, that means the last updated values.


Please provide a macro for this


No| Name| marks

1| Raj| 0

2| sree| 0

3| Raj| 0

4| lena| 0

5| Raj| 15

6| Sree| 12


Regards


Sreekhosh.AP

 

[Luke M]

Easiest way is probably using formulas. we could automate if needed.


1. Formula in helper column:

=COUNTIF(A2:A$10000,A2)>2


2. Filter this column for all "TRUE" values.

3. Delete all of these rows.

4. Unfilter

5. Remove helper column, if desired.

If you really want a macro, here's one to work on col A

[pre]

Sub KeepLastDupe()
Dim x() As String
Dim z As Variant
Dim LastRow As Long
Dim newValue As String

With ActiveSheet
LastRow = Cells(.Rows.Count, "A").End(xlUp).Row
End With

ReDim x(0 To LastRow)

Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
newValue = Cells(i, "A").Value

'Create a filtered array
z = Filter(x, newValue)

If UBound(z) >= 0 Then 'dupe found

'If dupe, delete the row
Cells(i, "A").EntireRow.Delete
Else
'otherwise, add value to our array for future reference
x(i) = newValue
End If
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[shrivallabha]

Here's one more approach.

[pre]

Option Explicit
Public Sub KeepLast()
Dim i As Long
Application.ScreenUpdating = False
With CreateObject("Scripting.Dictionary")
.comparemode = vbTextCompare
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
If .exists(Range("B" & i).Value) Then
Range("A" & i).EntireRow.Delete
Else
.Add Range("B" & i).Value, Range("B" & i).Value
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[/pre]

 

[sreekhosh]

Thank you very much shrivallabha and Luke M