Macro to delete rows if...
Hello,
I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
Macro to delete rows if...
Wouldn't Auto Filter does the job by deleting the rows after fitering for 0
on both columns? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Sasikiran" wrote: Hello, I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
Macro to delete rows if...
Hi Sasikiran
The below will only delete if 0's are present in G and H. Note the macro will not delete blank rows....Try and feedback Sub DeleteRows() For lngRow = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1 If Range("G" & lngRow).Text = "0" And Range("H" & lngRow).Text = "0" Then Rows(lngRow).Delete End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sasikiran" wrote: Hello, I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
Macro to delete rows if...
Assuming that your data start from row 2 with header in row 1,
this code will delete rows if only both column G and H have a value of 0 if either G or H have a value other than 0, it will remain in the sheet. try this in a module Sub RemoveZero() Dim r As Long For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1 If Cells(r, 7).Value = 0 And _ Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete Next r End Sub -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Sasikiran" wrote: Hello, I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
Macro to delete rows if...
Thank you so much... :)
"Jacob Skaria" wrote: Hi Sasikiran The below will only delete if 0's are present in G and H. Note the macro will not delete blank rows....Try and feedback Sub DeleteRows() For lngRow = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1 If Range("G" & lngRow).Text = "0" And Range("H" & lngRow).Text = "0" Then Rows(lngRow).Delete End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sasikiran" wrote: Hello, I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
Macro to delete rows if...
Thank you so much Francis for your inputs.. It's working
"Francis" wrote: Assuming that your data start from row 2 with header in row 1, this code will delete rows if only both column G and H have a value of 0 if either G or H have a value other than 0, it will remain in the sheet. try this in a module Sub RemoveZero() Dim r As Long For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1 If Cells(r, 7).Value = 0 And _ Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete Next r End Sub -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Sasikiran" wrote: Hello, I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
Macro to delete rows if...
Hi Sasikiran
Thanks for the feedback, glad I am of assistance. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Sasikiran" wrote: Thank you so much Francis for your inputs.. It's working "Francis" wrote: Assuming that your data start from row 2 with header in row 1, this code will delete rows if only both column G and H have a value of 0 if either G or H have a value other than 0, it will remain in the sheet. try this in a module Sub RemoveZero() Dim r As Long For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1 If Cells(r, 7).Value = 0 And _ Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete Next r End Sub -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Sasikiran" wrote: Hello, I need help with a macro that will delete certain rows in a spreadsheet. The rows that need to be deleted are never in the same place, or the same number of rows. The row that need to be deleted will have a value 0 in two columns G & H. Only if the value in the columns G & H is zero (0), the entire row should be deleted. If either of the columns has value other than 0 it should remain in the sheet. Please suggest me on this. |
All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com