| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete if Three Matches in Three Columns
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:D" & _ .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 arent deleted and I know I have to delete from the bottom up€¦but I dont think my code is doing that now. How can I change this to work correctly? Thanks, Ryan--- -- RyGuy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete if Three Matches in Three Columns
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 "ryguy7272" wrote in message ... 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:D" & _ .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 arent deleted and I know I have to delete from the bottom up€¦but I dont think my code is doing that now. How can I change this to work correctly? Thanks, Ryan--- -- RyGuy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete if Three Matches in Three Columns
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 "ryguy7272" wrote in message ... 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 "ryguy7272" wrote in message ... 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:D" & _ .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 arent deleted and I know I have to delete from the bottom up€¦but I dont think my code is doing that now. How can I change this to work correctly? Thanks, Ryan--- -- RyGuy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete if Three Matches in Three Columns
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 "ryguy7272" wrote in message ... 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 "ryguy7272" wrote in message ... 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:D" & _ .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 arent deleted and I know I have to delete from the bottom up€¦but I dont think my code is doing that now. How can I change this to work correctly? Thanks, Ryan--- -- RyGuy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding matches in two columns | Excel Worksheet Functions | |||
| Search for matches in two columns | Excel Worksheet Functions | |||
| Delete rows if value matches | Excel Programming | |||
| How can I delete all matches in a workbook? | Excel Worksheet Functions | |||
| Calling all MVPs! Macro to delete non matches b/t two lists. Thank | Excel Programming | |||
Linear Mode
