I am trying to loop through a data set (sorted by ColumnD, ColumnF, and
ColumnG), and trying to delete dupes in ColumnD, ColumnF, and ColumnG.


'Compare and delete if dupes
Dim rCell As Range
With ActiveSheet
For Each rCell In .Range("D1" & _
.Range("D" & .Rows.Count).End(xlUp).Row)
With rCell
If .Value = .Offset(0, 2).Value Then
If .Value = .Offset(0, 3).Value Then
rCell.EntireRow.Delete
Else
End If
End If
End With
Next rCell
End With

The dupes aren’t deleted and I know I have to delete from the bottom up…but
I don’t think my code is doing that now. How can I change this to work
correctly?

Thanks,
Ryan---


--
RyGuy

try this
Sub deleltedupcolumns()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
Rows(i).Delete
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"ryguy7272" <(E-Mail Removed)> wrote in message
news:F1B2B9CA-D0DA-436D-806A-(E-Mail Removed)...
>I am trying to loop through a data set (sorted by ColumnD, ColumnF, and
> ColumnG), and trying to delete dupes in ColumnD, ColumnF, and ColumnG.
>
>
> 'Compare and delete if dupes
> Dim rCell As Range
> With ActiveSheet
> For Each rCell In .Range("D1" & _
> .Range("D" & .Rows.Count).End(xlUp).Row)
> With rCell
> If .Value = .Offset(0, 2).Value Then
> If .Value = .Offset(0, 3).Value Then
> rCell.EntireRow.Delete
> Else
> End If
> End If
> End With
> Next rCell
> End With
>
> The dupes aren’t deleted and I know I have to delete from the bottom
> up…but
> I don’t think my code is doing that now. How can I change this to work
> correctly?
>
> Thanks,
> Ryan---
>
>
> --
> RyGuy

Well, thanks Don! That looks pretty sexy, but it didn't work as it was. I
made a few minor changes, and still no luck. Working with this now:

For i = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
If Cells(i, 6) And Cells(i, 7) = Cells(i, 4) Then
Rows(i).Delete
End If
Next i

I get a Run-time Error 13; type mismatch.
Any ideas?

Thanks,
Ryan---

--
RyGuy


"Don Guillett" wrote:

> try this
> Sub deleltedupcolumns()
> For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
> If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
> Rows(i).Delete
> Next i
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:F1B2B9CA-D0DA-436D-806A-(E-Mail Removed)...
> >I am trying to loop through a data set (sorted by ColumnD, ColumnF, and
> > ColumnG), and trying to delete dupes in ColumnD, ColumnF, and ColumnG.
> >
> >
> > 'Compare and delete if dupes
> > Dim rCell As Range
> > With ActiveSheet
> > For Each rCell In .Range("D1" & _
> > .Range("D" & .Rows.Count).End(xlUp).Row)
> > With rCell
> > If .Value = .Offset(0, 2).Value Then
> > If .Value = .Offset(0, 3).Value Then
> > rCell.EntireRow.Delete
> > Else
> > End If
> > End If
> > End With
> > Next rCell
> > End With
> >
> > The dupes aren’t deleted and I know I have to delete from the bottom
> > up…but
> > I don’t think my code is doing that now. How can I change this to work
> > correctly?
> >
> > Thanks,
> > Ryan---
> >
> >
> > --
> > RyGuy
>
>

Worked for me, AS TESTED, except for the email word wrap on the THEN. Excel
wants it this way
If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
or
If Cells(i, 6 = Cells(i, 4 )and Cells(i, 7) = Cells(i, 4) Then

Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"ryguy7272" <(E-Mail Removed)> wrote in message
news:06835084-4130-4DD1-9A54-(E-Mail Removed)...
> Well, thanks Don! That looks pretty sexy, but it didn't work as it was.
> I
> made a few minor changes, and still no luck. Working with this now:
>
> For i = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
> If Cells(i, 6) And Cells(i, 7) = Cells(i, 4) Then
> Rows(i).Delete
> End If
> Next i
>
> I get a Run-time Error 13; type mismatch.
> Any ideas?
>
> Thanks,
> Ryan---
>
> --
> RyGuy
>
>
> "Don Guillett" wrote:
>
>> try this
>> Sub deleltedupcolumns()
>> For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
>> If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
>> Rows(i).Delete
>> Next i
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "ryguy7272" <(E-Mail Removed)> wrote in message
>> news:F1B2B9CA-D0DA-436D-806A-(E-Mail Removed)...
>> >I am trying to loop through a data set (sorted by ColumnD, ColumnF, and
>> > ColumnG), and trying to delete dupes in ColumnD, ColumnF, and ColumnG.
>> >
>> >
>> > 'Compare and delete if dupes
>> > Dim rCell As Range
>> > With ActiveSheet
>> > For Each rCell In .Range("D1" & _
>> > .Range("D" & .Rows.Count).End(xlUp).Row)
>> > With rCell
>> > If .Value = .Offset(0, 2).Value Then
>> > If .Value = .Offset(0, 3).Value Then
>> > rCell.EntireRow.Delete
>> > Else
>> > End If
>> > End If
>> > End With
>> > Next rCell
>> > End With
>> >
>> > The dupes aren’t deleted and I know I have to delete from the bottom
>> > up…but
>> > I don’t think my code is doing that now. How can I change this to work
>> > correctly?
>> >
>> > Thanks,
>> > Ryan---
>> >
>> >
>> > --
>> > RyGuy
>>
>>

My data was a little screwed up before; didn't notice it. You were right and
I was wrong. Thanks so much!! Very helpful!!

Regards,
Ryan---

--
RyGuy


"Don Guillett" wrote:

> Worked for me, AS TESTED, except for the email word wrap on the THEN. Excel
> wants it this way
> If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
> or
> If Cells(i, 6 = Cells(i, 4 )and Cells(i, 7) = Cells(i, 4) Then
>
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:06835084-4130-4DD1-9A54-(E-Mail Removed)...
> > Well, thanks Don! That looks pretty sexy, but it didn't work as it was.
> > I
> > made a few minor changes, and still no luck. Working with this now:
> >
> > For i = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
> > If Cells(i, 6) And Cells(i, 7) = Cells(i, 4) Then
> > Rows(i).Delete
> > End If
> > Next i
> >
> > I get a Run-time Error 13; type mismatch.
> > Any ideas?
> >
> > Thanks,
> > Ryan---
> >
> > --
> > RyGuy
> >
> >
> > "Don Guillett" wrote:
> >
> >> try this
> >> Sub deleltedupcolumns()
> >> For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
> >> If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
> >> Rows(i).Delete
> >> Next i
> >> End Sub
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "ryguy7272" <(E-Mail Removed)> wrote in message
> >> news:F1B2B9CA-D0DA-436D-806A-(E-Mail Removed)...
> >> >I am trying to loop through a data set (sorted by ColumnD, ColumnF, and
> >> > ColumnG), and trying to delete dupes in ColumnD, ColumnF, and ColumnG.
> >> >
> >> >
> >> > 'Compare and delete if dupes
> >> > Dim rCell As Range
> >> > With ActiveSheet
> >> > For Each rCell In .Range("D1" & _
> >> > .Range("D" & .Rows.Count).End(xlUp).Row)
> >> > With rCell
> >> > If .Value = .Offset(0, 2).Value Then
> >> > If .Value = .Offset(0, 3).Value Then
> >> > rCell.EntireRow.Delete
> >> > Else
> >> > End If
> >> > End If
> >> > End With
> >> > Next rCell
> >> > End With
> >> >
> >> > The dupes aren’t deleted and I know I have to delete from the bottom
> >> > up…but
> >> > I don’t think my code is doing that now. How can I change this to work
> >> > correctly?
> >> >
> >> > Thanks,
> >> > Ryan---
> >> >
> >> >
> >> > --
> >> > RyGuy
> >>
> >>
>
>