Removing duplicates
 

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


--
Thanks
Sophie

Removing duplicates
 

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.
--
HTH,

Barb Reinhardt



"Sophie" wrote:

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


--
Thanks
Sophie

Removing duplicates
 

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

"Sophie" wrote:

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


--
Thanks
Sophie

Removing duplicates
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sophie" wrote in message
...
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


--
Thanks
Sophie

Removing duplicates
 

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$1:D1,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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP



"Sophie" wrote in message
...
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


--
Thanks
Sophie

Removing duplicates
 

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:D" & lngRow - 1), _
Range("D" & lngRow)) 0 Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Sophie" wrote:

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


--
Thanks
Sophie

Removing duplicates
 

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


--
Thanks
Sophie


"Mike H" wrote:

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

"Sophie" wrote:

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


--
Thanks
Sophie

Removing duplicates
 

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
"Sophie" wrote in message ...
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


--
Thanks
Sophie