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

Answer 1

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