On Apr 9, 12:16*pm, joel wrote:
Sub RemoveRows()

'first sort data in column D
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Rows("1:" & LastRow)
SortRange.Sort _
* *key1:=Range("D1"), _
* *Order1:=xlAscending, _
* *header:=xlYes

If Range("d2") < "" Then
* *LastRow = Range("D2").End(xlDown).Row
* *Rows("2:" & LastRow).Delete
End If
End Sub



" wrote:
Hi
I am looking for some VBA to do the following please. I have 4 columns
of data. Column D contains results of tests most of the columns have
the word “Negative” some are blank (ie test has not been carried out
yet) and some have a comment (free text). There is a Product number in
column C. I want to be able to compare the blank cells to the cells
with the word negative and identify any matches of product number just
leaving the rows with blank cells which have a matched product number
as follows.

Date * * * Item * * * * * *ProductNo * * * * * Result
01/01/09 * Screws * * * * * 2345 * * * Negative
02/01/09 * Washers * * * * * * * * *13658 * * *Negative
03/01/09 * Bolts * * * * * * 15896 * * Negative
05/01/09 * Screws * * * * * * 12345
09/01/09 * Washers * * * * * * * * * *13658
14/01/09 * Bolts * * * * * * *15896 * * * * * Not suitable
15/01/09 * Spanners * * * * * * * * * * * * * 56987 * * * * * *Negative

Giving

05/01/09 * Screws * * * * *12345
09/01/09 * Washers * * * * * * * * *13658

I hope this is possible.

Any help is greatly appreciated

Eddie- Hide quoted text -

- Show quoted text -

Thanks joel

Unfortunately this deletes all rows. It does not leave the rows where
there is a match in product number in column C.