Compare blank cells in a column
My last macro ggave the same results you had posted. I went back and read
your instructions and found that you only want one result (13658). here is
the modified code.
Sub RemoveRows()
'first sort data in column C and D
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = Rows("1:" & LastRow)
SortRange.Sort _
key1:=Range("C1"), _
Order1:=xlAscending, _
key2:=Range("D1"), _
Order2:=xlAscending, _
header:=xlYes
'put x in rows to delete
For RowCount = 2 To LastRow
If Range("D" & RowCount) < "" Or _
Range("C" & RowCount) < _
Range("C" & (RowCount - 1)) Then
Range("E" & RowCount) = "X"
End If
Next RowCount
'sort deleted rows to top of sheet
SortRange.Sort _
key1:=Range("E1"), _
Order1:=xlAscending, _
header:=xlYes
If Range("E2") < "" Then
LastRow = Range("E" & Rows.Count).End(xlUp).Row
Rows("2:" & LastRow).Delete
End If
End Sub
" wrote:
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.
|