Delete rows based on column value

[Michael Abrams]

I would like code to delete all rows in a spreadsheet where Column C = "DELETE ME".

It can have thousands of rows.

Thank you.

Michael

[HansV]

Assuming that the used region begins in A1:

Code: Select all

Sub DeleteRows()
    Dim rng As Range
    If Application.CountIf(Range("C:C"), "DELETE ME") > 0 Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
        Set rng = Range("A1").CurrentRegion
        rng.AutoFilter Field:=3, Criteria1:="DELETE ME"
        rng.Offset(1).Resize(rng.Rows.Count - 1).EntireRow.Delete
        rng.AutoFilter
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[Michael Abrams]

Perfect Hans.

Thank you so much !

MIchael