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

 

[Jacob Skaria]

The reason for error is because once you delete the row number shifts..

Try the below version.If you are looking to delete these rows then you dont
need a formula to be assigned...You can validate that in code and delete the
row

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = lngLastRow To 2 Step -1
If Sheets("DataInput").Range("A1") = Range("A" & lngRow) And _
Range("E" & lngRow) = "Spot Deal" Then Row(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes

 

[Seeker]

Hi Jacob,
Thanks for your prompt reply and sorry for the duplicated post as it showed
“services was temporary suspended†on first thread.
Debug stopped at 4th line and highlighted Range – the one before(“E†&
IngRow) saying not define Sub or Function?
Rgds

The reason for error is because once you delete the row number shifts..

Try the below version.If you are looking to delete these rows then you dont
need a formula to be assigned...You can validate that in code and delete the
row

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = lngLastRow To 2 Step -1
If Sheets("DataInput").Range("A1") = Range("A" & lngRow) And _
Range("E" & lngRow) = "Spot Deal" Then Row(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria

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

 

[Seeker]

Hi Jacob,
I found the reason, changed Row(IngRow).Delete to Rows(IngRow).Delete, now
it works. Thanks very much.
Rgds

Hi Jacob,
Thanks for your prompt reply and sorry for the duplicated post as it showed
“services was temporary suspended†on first thread.
Debug stopped at 4th line and highlighted Range – the one before(“E†&
IngRow) saying not define Sub or Function?
Rgds

The reason for error is because once you delete the row number shifts..

Try the below version.If you are looking to delete these rows then you dont
need a formula to be assigned...You can validate that in code and delete the
row

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = lngLastRow To 2 Step -1
If Sheets("DataInput").Range("A1") = Range("A" & lngRow) And _
Range("E" & lngRow) = "Spot Deal" Then Row(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria

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

 

[Jacob Skaria]

Cheers.

If this post helps click Yes
---------------
Jacob Skaria

Hi Jacob,
I found the reason, changed Row(IngRow).Delete to Rows(IngRow).Delete, now
it works. Thanks very much.
Rgds

Hi Jacob,
Thanks for your prompt reply and sorry for the duplicated post as it showed
“services was temporary suspended†on first thread.
Debug stopped at 4th line and highlighted Range – the one before(“E†&
IngRow) saying not define Sub or Function?
Rgds

The reason for error is because once you delete the row number shifts..

Try the below version.If you are looking to delete these rows then you dont
need a formula to be assigned...You can validate that in code and delete the
row

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = lngLastRow To 2 Step -1
If Sheets("DataInput").Range("A1") = Range("A" & lngRow) And _
Range("E" & lngRow) = "Spot Deal" Then Row(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


:

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