remove duplicate rows from specific data in a column; excel 2007


D

DS

I am trying to remove entire rows reletive to duplicate data in one specific
column

for example:

A B C D E F

1 a1 b1 c1 d1 e1 f1
2 a2 * c2 d2 e2 f2
3 a3 * c3 d3 e3 f3
4 a4 b4 c4 d4 e4 f4
5 a5 * c5 d5 e5 f5


so the result I want is to delete all corresponding rows containing
duplicate values in column B. For the example above I would want the program
to delete row 3 and 5 because row 2, 3 and 5 contain the same value (leaving
2 as the remaining unique data of *) of *.

I have about 4,000 rows that contain only ONE column of duplicate values yet
the delete duplicate values feature is not deleting the entire rows that
contain the specific duplicate value in the selected column. Selecting all
rows or select all does not fix the problem.

To clarify, the duplicate values in the column that I want to use to trigger
the program to delete the entire row(s) do not correspond with duplicate
values across the row itself.
 
Ad

Advertisements

M

Mike H

hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
Dim myrange As Range
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For r = lastRow To 2 Step -1
If Cells(r, 2).Value = Cells(r - 1, 2).Value Then
Cells(r, 2).EntireRow.Delete
End If
Next
End Sub

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top