Solved: Delete rows based on condition

[austenr]

What is the best way to test to delete a row based on a condition? I am trying to do it with a for next loop but the macro only deletes 5 rows at a time. code is:

Sub deletelines()
Dim myrange As Range
For Each myrange In Range("H:H")
If myrange.Value = "NO MATCH" Then
myrange.EntireRow.Delete
End If
Next myrange
End Sub :dunno

[Norie]

Does this work any better?


Sub deletelines()
Dim myrange As Range
Dim LastRow As Long
Dim I As Long
LastRow = Range("H" & Rows.Count).End(xlUp).Row

For I=LastRow to 1 Step -1
Set myrange = Range("H" & I)
If myrange.Value = "NO MATCH" Then
myrange.EntireRow.Delete
End If
Next i
End Sub

[austenr]

Yes much. What was making mine not work?

[Norie]

Not 100% sure but generally you need to start at the bottom when deleting.

The problem is that when you delete a row references get changed and rows get missed.