Removing duplicates

[Sophie]

Greetings from a Newbie

In my workbook, Sheet1 has a table with several columns and thousands of
rows. I want to delete any rows that contain a duplicate value in column D.
For example, the first time 87E3Y appears in column D - that row is kept.
But, any subsequent times 87E3Y appears in column D, those rows are deleted.

I currently sort all data on Column D, Ascending, then remove the duplicates
by hand. Because I do this every month, I'd like some code to speed this up!

Thank you in advance
Sophie

 

[Barb Reinhardt]

One way to speed it up "manually" would be to add a helper column with the
following formula

=COUNTIF($D$1:$D2,$D2)

assuming you are entering this in Row 2. And copy down. Filter all
values int hat column for values greater than 1. Delete all values that are
greater than 1.

I know you're asking for code, but this is another trick that could help.

 

[Mike H]

Sophie,

try this

Sub delete_Me()
Dim LastRow As Long, x As Long, y As Long
LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Dim CopyRange As Range
For x = 1 To LastRow
For y = x + 1 To LastRow
If UCase(Cells(y, 4).Value) = UCase(Cells(x, 4).Value) And _
Cells(x, 4).Value <> "" Then
If CopyRange Is Nothing Then
Set CopyRange = Rows(y).EntireRow
Else
Set CopyRange = Union(CopyRange, Rows(y).EntireRow)
End If
End If
Next
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub


Mike

 

[Don Guillett]

Option Explicit
Sub deletesortedduplicates()
Dim i As Long
Dim MC As Long
MC = 4 'col D
For i = Cells(Rows.Count, MC).End(xlUp).Row To 8 Step -1
If Cells(i - 1, MC) = Cells(i, MC) Then Rows(i).Delete
Next i
End Sub

 

[Bernard Liengme]

Here is a non-VBA way
Suppose my data looks the first two columns (D and E) below
a 1 1
b 2 1
w 3 1
a 4 2
c 5 1
d 6 1
w 7 2


In F1 I have =COUNTIF($D$11,D1) and I copy this down the column by double
clicking the fill handle
I select all the F entries and use Copy followed by Paste Special-> Value to
change formulas to values
Now I select all the data (click on any cell within the data and then use
CTRL+A); use Data | Sort and sort by column F. The data looks like
a 1 1
b 2 1
w 3 1
c 5 1
d 6 1
a 4 2
w 7 2

Delete all the rows below the last value 1 in column F; Delete column F

You might experiment with recording a macro to do all but the last step.
best wishes

 

[Jacob Skaria]

Try the below macro for unsorted data....

Sub DeleteRows_Sophie()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(Range("D1" & lngRow - 1), _
Range("D" & lngRow)) > 0 Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes

 

[Sophie]

Mike - Much thanks to you and the other contributors. I used your code and
it worked flawlessly! For my own education, I'll try each of the other
solutions offered to weigh their merits.

Sophie

 

[helene and gabor]

Hello Sophie,

On my 2007 Excel I click on: Data and Remove Duplicates after marking one or several columns on the Excel sheet.
Simple and works!

best wishes

Gabor Sebo