Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows using a macro | Excel Discussion (Misc queries) | |||
macro to delete rows | Excel Discussion (Misc queries) | |||
Need a macro to delete rows | Excel Discussion (Misc queries) | |||
Macro to Delete Certain Rows | Excel Discussion (Misc queries) | |||
delete rows using macro | Excel Worksheet Functions |