Select and Delete
Following code was adopted from thread and modified to select rows with Y
in column I. The problem is not all rows with Y are deleted when range of
rows has found Y (say row 10 to 16 all has Y), it is fine if single row is
found with Y.
Sheets("Data").Select
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("I1").Formula = "=if(and(RC[-8]=DataInput!R1C1,RC[-4]=""Spot
Deal""),""Y"",""N"")"
Range("I1").Copy
Range("I1:I" & lastrow).Select
ActiveSheet.Paste
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
Set myrange = Range("I2:I" & lastrow)
For Each Count In myrange
If Count.Value = "Y" Then
Count.EntireRow.Delete
End If
Next
|