Select and Delete

  • Thread starter Thread starter Seeker
  • Start date Start date
S

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
 
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
 
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


Jacob Skaria said:
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


Seeker said:
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
Click to expand...
Click to expand...
 
Hi Jacob,
I found the reason, changed Row(IngRow).Delete to Rows(IngRow).Delete, now
it works. Thanks very much.
Rgds

Seeker said:
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


Jacob Skaria said:
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


Seeker said:
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
Click to expand...
Click to expand...
Click to expand...
 
Cheers.

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


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

Seeker said:
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


Jacob Skaria said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top