Tip: If you get a headache when you see the code then you can try my EasyFilter add-in.
This add-in have a option to delete rows.(there is no version for Excel 2007 on this moment)
You can find it here: http://www.rondebruin.nl/easyfilter.htm


There are a few different ways to delete rows :

Loop backwards through all rows
Loop backwards through all rows and use Union
Use AutoFilter to delete rows
Use Find to delete rows
Use Specialcells to delete rows

Tip: Download a workbook with the whole webpage and all code examples
in a separate module in the VBA editor. You can test the code in this workbook.
Download DeleteCode.zip


Loop backwards through all rows

You find different examples on this part of the page to loop through the rows and check
If a specific value exists in a column (A column in my example) and delete that row if it exists.

The example macro loop through all the rows in the UsedRange.
This is the first row with data till the last row with data on your worksheet.

It can be slower if the column that you check doesn’t use so many rows or if your UsedRange is
bigger then you data. You can find more information on Debra Dalgleish's website about this. http://www.contextures.on.ca/xlfaqApp.html#Unused


You can replace this part of the macro

Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

With this if you want to fill in the Firstrow and the Lastrow yourself.

Firstrow = 4
Lastrow = 100

Or with this if you want to fill in the Firstrow and let the code find the Lastrow in the column.

Firstrow = 1
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row


Code example

The code below will delete every row in the usedrange with "ron" in the A column.
If .Value = "ron" Then .EntireRow.Delete

I use the A column in my example, change the A to your column in this code line.
With .Cells(Lrow, "A")

Change ron and the A to your search word and column before you try the macro.
See the examples below the macro if you want to do something different.

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Examples to change the code


All macro’s will delete rows with "ron" in the A column

      If .Value = "ron" Then .EntireRow.Delete
      'This will delete each row with the Value "ron" in Column A, case sensitive.

You can replace the two lines above with one of the examples below
Note: I use text examples like ="ron" but you can also use = 1200 for
numeric columns or use >, <, >=, <=

Tip: If you want to delete all rows that not have "ron" in column A then replace = for <>


If .Value Like "*ron*" Then .EntireRow.Delete
'This will delete each row where "ron" is a part of the string, case sensitive.
' Use "*ron" for a value that start with ron or "ron*" for a value that ends with ron

If LCase(.Value) =  LCase("ron") Then .EntireRow.Delete
'This will delete each row with the Value "ron" in Column A, not case sensitive.

If Trim(.Value) = "ron" Then .EntireRow.Delete
'It uses the Trim function to strip the spaces
'This will delete each row with the Value "ron" in Column A, case sensitive.

If Trim(LCase(.Value)) =  LCase("ron") Then .EntireRow.Delete
'It uses the Trim function to strip the spaces
'This will delete each row with the Value "ron" in Column A, not case sensitive.

If .Value = "" Then .EntireRow.Delete
'This will delete each row if the cell is empty or have a formula that evaluates to ""

If IsEmpty(.Value) Then .EntireRow.Delete
'This will delete the row if the cell is empty

Select Case .Value
Case Is = "jelle", "ron", "dave": .EntireRow.Delete
End Select
'Use Select Case if you want to check more values in the cell

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave")  with  Sheets("Sheet1").Range("A1:A200")

Tip: To make it easier to edit you can define a variable to hold that array
ArrNames = Array("jelle", "ron", "dave")
If Not IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete

Do not forget to add this dim line
Dim ArrNames As Variant



Check a whole row or more columns


Replace this part of the macro with one of the code examples below

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

Replace the code above with the code below


If .Cells(Lrow, "A").Value = "ron" And _
.Cells(Lrow, "B").Value = "dave" And _
.Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
'Use this if you want to check for values in other columns also.

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the whole row is empty (all columns)

If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "E"))) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the first 5 cells in the row are empty

If Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the cells in A, M and X in the row are empty

If Application.CountIf(.Rows(Lrow), "ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "ron" not exist in the row (It will look in the whole row)

If Application.CountIf(.Rows(Lrow), "ron") > 0 Then .Rows(Lrow).Delete
' Delete each row if the value "ron" exist in the row (It will look in the whole row)

Tip: With CountIf you can use wild cards like this "*ron*" (is also working then if "ron" is a part of a cell)
Use "*ron" for a value that start with ron or "ron*" for a value that ends with ron.
See the Excel help for more information about CountIf, there are examples there
about other wildcards like ?.



Loop backwards through all rows and use Union


This example will loop through all rows in the usedrange and use Union to make a range (rng)
with all cells with the value "ron". Then it delete the rows in one time with rng.EntireRow.delete,
this will be faster then deleting in the loop like we do in the example above.

Note: you can use a lot of tips/examples from the example above in this union example.
If you have problems you can always ask a question in the Excel newsgroups.

Note: There is a maximum of 8192 separate areas when you use Union.
See this page for more info  http://www.rondebruin.nl/specialcells.htm


Code example

The code below will delete every row in the usedrange with "ron" in the A column.
If .Value = "ron" Then .EntireRow.Delete

I use the A column in my example, change the A to your column in this code line.
With .Cells(Lrow, "A")

Change ron and the A to your search word and column before you try the macro.

Sub Union_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim rng As Range

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then
                        'This will delete each row with the Value "ron"
                        'in Column A, case sensitive.

                        If rng Is Nothing Then
                            Set rng = .Cells
                        Else
                            Set rng = Application.Union(rng, .Cells)
                        End If
                    End If

                End If
            End With

        Next Lrow

    End With

    'Delete all rows in one time
    If Not rng Is Nothing Then rng.EntireRow.Delete

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Use AutoFilter to delete rows

Tip: with AutoFilter you can also use wildcards.

"*ron"  End with ron
"ron*"  Start with ron
"*ron*" ron is a part of the string

You can also use the wildcard ? for a single character.

Note: in the examples I use the range .Range("A1:A" & .Rows.Count)
Remember that A1 of this range is your header cell.


One criteria

The example below filter A1:A? on the ActiveSheet for the DeleteValue and delete the rows.

Sub Delete_with_Autofilter()
    Dim DeleteValue As String
    Dim rng As Range
    Dim calcmode As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'Fill in the value that you want to delete
    'Tip: use DeleteValue = "<>ron" to delete rows without ron
    DeleteValue = "ron"

    'Sheet with the data, you can also use Sheets("MySheet")
    With ActiveSheet

        'Firstly, remove the AutoFilter
        .AutoFilterMode = False

        'Apply the filter
        .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With

        'Remove the AutoFilter
        .AutoFilterMode = False
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub

Two criteria

The example below filter A1:A? on the ActiveSheet for the DeleteValue1 and 2 and delete the rows.

Sub Delete_with_Autofilter_Two_Criteria()
    Dim DeleteValue1 As String
    Dim DeleteValue2 As String
    Dim rng As Range
    Dim calcmode As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'Fill in the two values that you want to delete
    DeleteValue1 = "ron"
    DeleteValue2 = "jelle"

    'Sheet with the data, you can also use Sheets("MySheet")
    With ActiveSheet

        'Firstly, remove the AutoFilter
        .AutoFilterMode = False

        'Apply the filter
        .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, _
        Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2

        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With

        'Remove the AutoFilter
        .AutoFilterMode = False
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub

Example to filter between two dates, you not use the DeleteValue1 and 2 string then.
This will delete all records in 2006. (Note: I use xlAnd here).
You can use this also if you want to filter numeric values.

.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(2006, 1, 1), _
Operator:=xlAnd, Criteria2:="<" & DateSerial(2007, 1, 1) ' yyyy-mm-dd format



More then two Criteria

For more then two Criteria you can loop through the words in the array.
Note: This is also working if you use one word in the array.

Sub Delete_with_Autofilter_Array()
    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'Fill in the values that you want to delete
    myArr = Array("ron", "Dave", "Jelle")

    For I = LBound(myArr) To UBound(myArr)

        'Sheet with the data, you can also use Sheets("MySheet")
        With ActiveSheet

            'Firstly, remove the AutoFilter
            .AutoFilterMode = False

            'Apply the filter
            .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)

            Set rng = Nothing
            With .AutoFilter.Range
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then rng.EntireRow.Delete
            End With

            'Remove the AutoFilter
            .AutoFilterMode = False
        End With

    Next I

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub

Criteria range on a different sheet

Example with the criteria range on a different sheet

The example below filter A1:A? In a sheet named “data”.
And use as criteria all the cells in column A of a sheet named “Criteria”.
Note: You can use also wildcards like *food* or *store if you want.

Sub Delete_with_Autofilter_More_Criteria()
    Dim rng As Range
    Dim cell As Range
    Dim CriteriaRng As Range
    Dim calcmode As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Sheets("Criteria")
        Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
    End With

    'Loop through the cells in the Criteria range
    For Each cell In CriteriaRng

        With Sheets("data")

            'Firstly, remove the AutoFilter
            .AutoFilterMode = False

            'Apply the filter
            .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value

            With .AutoFilter.Range
                Set rng = Nothing
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then rng.EntireRow.Delete
            End With

            'Remove the AutoFilter
            .AutoFilterMode = False
        End With

    Next cell

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
    
End Sub

Use Find to delete rows


Change this three lines in the macro before you test it.

'We use the ActiveSheet but you can also use Sheets("MySheet")
Set sh = ActiveSheet

'We look in column A in this example
Set myRng = sh.Range("A:A")

'Add more search strings if you need
myStrings = Array("Ron", "Dave", "Tom")

Sub Find_Example()
    Dim calcmode As Long
    Dim ViewMode As Long
    Dim myStrings As Variant
    Dim FoundCell As Range
    Dim I As Long
    Dim myRng As Range
    Dim sh As Worksheet

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can also use Sheets("MySheet")
    Set sh = ActiveSheet

    'We search in column A in this example
    Set myRng = sh.Range("A:A")

    'Add more search strings if you need
    myStrings = Array("Ron", "Dave", "Tom")


    With sh

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'We will search the values in MyRng in this example
        With myRng

            For I = LBound(myStrings) To UBound(myStrings)
                Do
                    Set FoundCell = myRng.Find(What:=myStrings(I), _
                                               After:=.Cells(.Cells.Count), _
                                               LookIn:=xlFormulas, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                    'Use xlPart If you want to search in a part of the FoundCell
                    'If you use LookIn:=xlValues it will also delete rows with a
                    'formula that evaluates to "Ron"
                    If FoundCell Is Nothing Then
                        Exit Do
                    Else
                        FoundCell.EntireRow.Delete
                    End If
                Loop
            Next I

        End With

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub

Use Specialcells to delete rows

You can find information on this page.
http://www.rondebruin.nl/specialcells.htm