Select and Delete

[Seeker]

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

 

[Mike H]

Hi,

Modify the loop thaat does the deleting to this

lastrow = Cells(Rows.Count, "I").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 9) = "Y" Then
Rows(x).EntireRow.Delete
End If
Next

Mike