Sub deleterows()
Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "AI"), Cells(Rows.Count, "AI").End(xlUp))
'Work backwards from bottom to top when deleting rows
Application.ScreenUpdating = False
With rng
For i = .Rows.Count To 1 Step -1
If .Cells(i) = "Ongoing" Then
.Cells(i).EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

"Tim" wrote:

Hi,

Its been a while since i've used VBA so i apologise if i'm doing something
stupid. [XP Pro Excel2002 SP3]

i have a sheet of data. one column may have many entries of 'Ongoing'.
where this occurs, i want to delete the entire row. i've tried the
following: -
'=============
Sub rem_ongoing()
Dim cell As Range
Columns("AI").Select

For Each cell In Selection
If cell.Value = "Ongoing" Then
cell.EntireRow.Delete
End If
Next cell
End Sub
'=============

it works, BUT does not delete all relevant rows (maybe because i'm working
top-down and its missing the row after the one its just deleted?) and is
pretty slow (approx 3000 rows of data, 64 of which contain 'Ongoing' takes
approx 30-40 seconds to complete).

can someone confirm my theory about missing rows and can anyone recommend a
speedier alternative?

thanks

Tim