Deleting duplicated rows based on data in two columns

M

Monk

I have a range of data in cells A1:K1000 and some of the row data in that
range has been partially duplicated. What I would like to do is to delete
the duplicated row if the cell values in column A are the same as well as the
data in Column E.

For example:
If cell A1 (Smith) and A2 (Smith) are the same and cells E1 (Microsoft) and
E2 (Microsoft) are the same, delete row 2. However If cell A1 (Smith) and A2
(Smith) are the same and cells E1 (Microsoft) and E2 (Apple) are different,
then this is not a duplicate so it should not be deleted.

Any assistance would be appreciated.
 
J

JBeaucaire

Try this:

========
Sub DeleteDupes()
'JBeaucaire (11/16/2009)
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") And _
Cells(i, "E") = Cells(i - 1, "E") Then _
Rows(i).Delete xlShiftUp
End If
Next i

Application.ScreenUpdating = True
End Sub
========

Does that help?
 
O

om

Hi

Easier approach to do this - -

Data>Filter>Advanced Filter --select Unique records only

This will filter the unique records.. then u can select copy to another
location

Regards
Om
 
J

JBeaucaire

How would you use Adv Filter to select unique based on columns A and E only
at the same time?
 
M

Monk

I got a compile error on the End if line so I removed that and it works
perfectly. Exactly what I was looking for. Much appreciated.
 
J

JBeaucaire

Sorry, I noticed that error after I'd posted, and you can't edit these
newsgroup posting. Glad you spotted it.

Your feedback is appreciated, click YES if this post helped you.

-Jerry
 

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