|
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
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" 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 |
Select and Delete
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" wrote: 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" 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 |
Select and Delete
Hi Jacob,
I found the reason, changed Row(IngRow).Delete to Rows(IngRow).Delete, now it works. Thanks very much. Rgds "Seeker" wrote: 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" wrote: 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" 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 |
Select and Delete
Cheers.
If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: Hi Jacob, I found the reason, changed Row(IngRow).Delete to Rows(IngRow).Delete, now it works. Thanks very much. Rgds "Seeker" wrote: 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" wrote: 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" 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 |
| All times are GMT +1. The time now is 12:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com