VBA – Delete rows based on condition

One of the most common questions I get is how to delete rows based on one or more conditions. For instance, you want to delete all rows in your sheets that have the name “John” in column A. Here’s the code to do that:

Sub Delete()

    Dim startrow As Long
    'starting row number here
    startrow = 1
    ' Assuming data to check is in A Column
    Do Until startrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Cells(startrow, 1).Value = "John" Then
            Rows(startrow).Delete
        Else
            startrow = startrow + 1
        End If
    Loop

End Sub

1 comentários:

Lucas Gilchrist said...

When I ran this code, it deleted all the rows including the "a".

Sub DeleteRows()

Dim startrow As Long
'starting row number here
startrow = 4
' Assuming data to check is in A Column
Do Until startrow > Cells(Cells.Rows.Count, "C").End(xlUp).Row
If Cells(startrow, 1).Value <> "a" Then
Rows(startrow).Delete
Else
startrow = startrow + 1
End If
Loop

End Sub