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.

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?

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

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


"Monk" wrote:

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

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

"JBeaucaire" wrote:

> 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?
>
> --
> "Actually, I *am* a rocket scientist." -- JB
> (www.MadRocketScientist.com)
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Monk" wrote:
>
> > 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.

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

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


"om" wrote:

> 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

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.

"JBeaucaire" wrote:

> 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?
>
> --
> "Actually, I *am* a rocket scientist." -- JB
> (www.MadRocketScientist.com)
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Monk" wrote:
>
> > 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.

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

"Monk" wrote:

> 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.
>
> "JBeaucaire" wrote:
>
> > 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?
> >
> > --
> > "Actually, I *am* a rocket scientist." -- JB
> > (www.MadRocketScientist.com)
> >
> > Your feedback is appreciated, click YES if this post helped you.
> >
> >
> > "Monk" wrote:
> >
> > > 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.