Deleting rows with duplicate info in columns

Question

I'm writing a code that copies data from one sheet into another and I've got that function working fine. Now, I'm trying to code it to delete any rows that contain duplicate information based off that information's ID number in column F. Part of our process is to manually enter in column E when each row has been worked.

So my end goal is for the code to delete rows where column E is blank and column F is a duplicate. My code runs, but doesn't delete anything. I'm really hoping I'm just missing something ridiculously obvious.

For i = 1 To Range("f" & Rows.Count).End(xlUp).Row
    If Cells(i, 5).Value = "" Then 'if column E is blank on row i
        x = Cells(i, 6).Value
        If Not IsError(Application.Match(x, "F:F", 0)) Then '& if that row is a duplicate
            ActiveSheet.Range(x).EntireRow.Delete 'delete new duplicate row
        End If
    End If
Next i

Try `ActiveSheet.Cells(i, 6).EntireRow.Delete` – findwindow May 11 '16 at 18:10 — findwindow, May 11 '16 at 18:10

score 5 · Answer 1 · answered May 11 '16 at 18:12

5

Try it with,

For i = Range("f" & Rows.Count).End(xlUp).Row to 1 Step -1
    If Cells(i, 5).Value = "" Then 'if column E is blank on row i
        x = Cells(i, 6).Value
        If Application.Countif(Columns(6), x) > 1 Then '& if that row is a duplicate
            Rows(i).EntireRow.Delete 'delete new duplicate row
        End If
    End If
Next i

You were trying to delete the row number x, not i. Additionally, everything was going to be matched once.

answered May 11 '16 at 18:12

You beat me to the bunch. I need to type more quickly!! – basodre May 11 '16 at 18:14
@user3561813 you had a longer explanation ^_^ – findwindow May 11 '16 at 18:14
1

@findwindow maybe I can change my username to `verbosity`! – basodre May 11 '16 at 18:16
2

@user3561813 - I don't see any issue with similar answers being posted within a short timeframe and fw is correct; you took the time to expand on 'best practise' corrections like working bottom-to-top that I only corrected in code. The cream will rise to the top. – May 11 '16 at 18:20
Ugh Batman, youtube blocked at work. But yeaaaaa, 3-3 now. Pay per view cage fight commencing. Please paypal tickets to my email kthxbye. – findwindow May 11 '16 at 18:26
I just put him over! He's in the lead now! – basodre May 11 '16 at 18:48
@user3561813 I am trying to sell tickets and you ruin it. tsk tsk. Removing my upvote from you =P – findwindow May 11 '16 at 18:49
1

See it's conversations like this that make me love, not just use, this site. ;) Thanks for the assist. I'll test and confirm it works. – Tommy M May 11 '16 at 18:55
1

@TommyM Make sure you use your upvotes wisely! We need to make sure findwindow maximizes ticket sales – basodre May 11 '16 at 19:14

score 4 · Accepted Answer · answered May 11 '16 at 18:13

So there are a couple of errors that need to be addressed in your code. First, if you are looping over a range and deleting rows, it's best to start from the bottom and work your way up. This prevents issues where your iterator is on a row, that row gets deleted, and the loop essentially skips the next row.

Next, you are looking for a Match in column F of x, which contains a value from Column F. So, it will always return a value (itself, at the very minimum). Maybe try using a COUNTIF and seeing if it's greater than 1 may be a better option?

Next, you populated the variable x with the value in Cells(i, 6), but then you try to use it as a range when deleting. Change your code to the following and see if it works:

For i = Range("f" & Rows.Count).End(xlUp).Row To 1 Step -1
If Cells(i, 5).Value = "" Then 'if column E is blank on row i
x = Cells(i, 6).Value
    If Application.Countif(Columns(6), x) > 1 Then '& if that row is a duplicate
    ActiveSheet.Rows(i).Delete 'delete new duplicate row
    End If
End If
Next i

Highly appreciate the added info/best practices details. I'd upvote for that alone. "Teach a man to fish..." — Tommy M, May 11 '16 at 18:56

score 0 · Answer 3 · answered May 11 '16 at 20:52

0

Why not use the .RemoveDuplicates method? It's faster than looping around. Here's a rough outline on its use:

With Range
    .RemoveDuplicates Columns:=Array(6), Header:=xlYes
End With

Here's the msdn doc for the method, and another page with a more detailed implementation. They should clear up any questions you might have.

answered May 11 '16 at 20:52

gualdhar

3
4

Column E has to be blank as well as values in column F being duplicated. While using column E and F for the duplicate criteria **might** work, it could lead to false positives. – May 11 '16 at 21:11

Deleting rows with duplicate info in columns

3 Answers3