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

Select and Delete
 

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

"Seeker" wrote:

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