Deleting Rows with Sum of "0" in 4 columns

[mike meyer]

A B C D E
Product Sub Sub Sub Sub
Code Code Code Code Code
-1 -2 -3
-4
111 0 0 0 25
222 0 0 0
0
333 5 1 0
0
444 0 0 0
0
555 1 0 0
0
666 0 0 1
0

In the above example I have:
25 of Product Code 111-4
0 of any Product Code 222
5 of Product Code 333-1 and 1 of Product Code 333-2
0 of any Product code 444
1 of Product Code 555-1 and 1 of Product Code 666-3.

Each box contains a formula that refers to other sheets. It is not blank,
but the result of the formula is zero.

I would like to delete the rows with the 222's and the 444's because the sum
of columns B thru E is zero.

Is there a way to do this via a Macro.
Thanks,
Mike

 

[Tom Ogilvy]

Can't tell exactly how your data is laid out, but

assuming delete rows where sum of B:E is zero:

Sub TestDeletion()
Dim lastrow as Long, i as Long
set lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if application.Sub(cells(i,2).Resize(1,4)) = 0 then
rows(i).Delete
end if
Next
End Sub

Test this on a copy of your sheet.

 

[mike meyer]

Sorry Tom,
I get Compile Error:
Object required
and "lastrow =" is highlighted.

You DO understand the concept, though,
I want to remove rows where the sum of B:E is zero.
Thanks,
Mike

Can't tell exactly how your data is laid out, but

assuming delete rows where sum of B:E is zero:

Sub TestDeletion()
Dim lastrow as Long, i as Long
set lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if application.Sub(cells(i,2).Resize(1,4)) = 0 then
rows(i).Delete
end if
Next
End Sub

Test this on a copy of your sheet.

 

[Ron de Bruin]

For the OP

Typo alert

if application.Sub

Must be
if application.Sum

 

[Tom Ogilvy]

I have a bad habit of using

Set lastrow =,

The set should be removed:

Sub TestDeletion()
Dim lastrow as Long, i as Long
lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if application.Sub(cells(i,2).Resize(1,4)) = 0 then
rows(i).Delete
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"mike meyer" <mikejmeyer53atexcite.com> wrote in message
Sorry Tom,
I get Compile Error:
Object required
and "lastrow =" is highlighted.

You DO understand the concept, though,
I want to remove rows where the sum of B:E is zero.
Thanks,
Mike

Can't tell exactly how your data is laid out, but

assuming delete rows where sum of B:E is zero:

Sub TestDeletion()
Dim lastrow as Long, i as Long
set lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if application.Sub(cells(i,2).Resize(1,4)) = 0 then
rows(i).Delete
end if
Next
End Sub

Test this on a copy of your sheet.

 

[Tom Ogilvy]

Whew, bad day: -- as Ron said

Sub TestDeletion()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
For i = lastrow To 2 Step -1
If Application.Sum(Cells(i, 2).Resize(1, 4)) = 0 Then
Rows(i).Delete
End If
Next
End Sub


I tested that one and it worked.

 

[mike meyer]

Tom,
Thanks! Works Beautifully!
I made your change and the "Application.Sum" Typo correction from Ron de
Bruin
and it cleaned up all the extraneous rows. I even halfway understand how it
works.
Thanks a lot!
Mike
I have a bad habit of using

Set lastrow =,

The set should be removed:

Sub TestDeletion()
Dim lastrow as Long, i as Long
lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if application.Sub(cells(i,2).Resize(1,4)) = 0 then
rows(i).Delete
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"mike meyer" <mikejmeyer53atexcite.com> wrote in message
Sorry Tom,
I get Compile Error:
Object required
and "lastrow =" is highlighted.

You DO understand the concept, though,
I want to remove rows where the sum of B:E is zero.
Thanks,
Mike

Can't tell exactly how your data is laid out, but

assuming delete rows where sum of B:E is zero:

Sub TestDeletion()
Dim lastrow as Long, i as Long
set lastrow = cells(rows.count,2).End(xlup).row
for i = lastrow to 2 Step -1
if application.Sub(cells(i,2).Resize(1,4)) = 0 then
rows(i).Delete
end if
Next
End Sub

Test this on a copy of your sheet.

 

[Ron de Bruin]

Hi Tom

Whew, bad day

Glad I am not the only one<vbg>