CODEWITHSUNDEEP
vbaexcelrowdeleting
Tommy M
Tommy M

Reputation: 33

Deleting rows with duplicate info in columns

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

Upvotes: 3

Views: 102

Answers (3)

gualdhar
gualdhar

Reputation: 3

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.

Upvotes: 0

basodre
basodre

Reputation: 5770

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

Upvotes: 4

user4039065
user4039065

Reputation:

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.

Upvotes: 5

Related Questions