Hi Tim,

Your suspicions are correct.
This should do you; it creates an array of the rows and deletes them
all in one go - which should be much quicker.

Sub Remove_Unwanted_Rows()
'Highlight range of cells on wksheet;this deletes
'all rows with cells exactly matching cases
Dim Cell, RowArray As Range
For Each Cell In Selection
Select Case Cell
Case Is = "Ongoing" '& any others you like separted by commas
If RowArray Is Nothing Then
Set RowArray = Cell.EntireRow
Else
Set RowArray = Union(RowArray, Cell.EntireRow)
End If
End Select
Next Cell
RowArray.Delete
End Sub

Cheers,
JF

On 18 Nov, 15:18, "Tim" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk 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