If sum = 0, delete row
 

I have a spreadsheet with amounts that start in column C and goes through
column AE (columns A & B contain an entity number and an entity name). The
number of rows varies, but it is typically about 600 rows of data. This data
begins on row 9. Row 8 contains the column headers and the 1st 7 rows
contain junk that comes over from the program I download this data from. It
can be eliminated if it helps.

Basically I am looking for a macro that steps through each row of data and
if the sum of column C through column AE = zero, I would like the entire row
to be deleted.

Does anybody have any ideas? I feel bad because this is the third time I
have come to this group for help. Each time the solutions have been
terriffic. I keep hoping I can answer a question instead of always asking,
but so far all the questions asked have been above my skill level.

Thanks,

Tim

If sum = 0, delete row
 

Dim rw as Long, i as Long
rw = cells(rows.count,1).End(xlup).Row
for i = rw to 9 step -1
if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then
rows(rw).Delete
end if
Next

Hopefully your data actually contains numbers and not numbers stored as
strings.
--
Regards,
Tom Ogilvy


"TJN" wrote in message
...
I have a spreadsheet with amounts that start in column C and goes through
column AE (columns A & B contain an entity number and an entity name).
The
number of rows varies, but it is typically about 600 rows of data. This
data
begins on row 9. Row 8 contains the column headers and the 1st 7 rows
contain junk that comes over from the program I download this data from.
It
can be eliminated if it helps.

Basically I am looking for a macro that steps through each row of data and
if the sum of column C through column AE = zero, I would like the entire
row
to be deleted.

Does anybody have any ideas? I feel bad because this is the third time I
have come to this group for help. Each time the solutions have been
terriffic. I keep hoping I can answer a question instead of always
asking,
but so far all the questions asked have been above my skill level.

Thanks,

Tim

If sum = 0, delete row
 

Tom -
I punched this in and it deletes only the very last row of data (which
happens to sum to zero - I didn't test to see what happens if it doesn't sum
to zero). I was messing around with an Offset command to move up a row after
the if..then statement, but I can't seem to get it to work. Am I missing
something?

Thanks for your help.

Tim

"Tom Ogilvy" wrote:

Dim rw as Long, i as Long
rw = cells(rows.count,1).End(xlup).Row
for i = rw to 9 step -1
if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then
rows(rw).Delete
end if
Next

Hopefully your data actually contains numbers and not numbers stored as
strings.
--
Regards,
Tom Ogilvy


"TJN" wrote in message
...
I have a spreadsheet with amounts that start in column C and goes through
column AE (columns A & B contain an entity number and an entity name).
The
number of rows varies, but it is typically about 600 rows of data. This
data
begins on row 9. Row 8 contains the column headers and the 1st 7 rows
contain junk that comes over from the program I download this data from.
It
can be eliminated if it helps.

Basically I am looking for a macro that steps through each row of data and
if the sum of column C through column AE = zero, I would like the entire
row
to be deleted.

Does anybody have any ideas? I feel bad because this is the third time I
have come to this group for help. Each time the solutions have been
terriffic. I keep hoping I can answer a question instead of always
asking,
but so far all the questions asked have been above my skill level.

Thanks,

Tim

If sum = 0, delete row
 

A couple of typos - try this which worked in my test:

Sub ABCD()
Dim rw As Long, i As Long
rw = Cells(Rows.Count, 1).End(xlUp).Row
For i = rw To 9 Step -1
If Application.Sum(Cells(i, 3).Resize(1, 29)) = 0 Then
Rows(i).Delete
End If
Next

End Sub

--
regards,
Tom Ogilvy

"TJN" wrote in message
...
Tom -
I punched this in and it deletes only the very last row of data (which
happens to sum to zero - I didn't test to see what happens if it doesn't
sum
to zero). I was messing around with an Offset command to move up a row
after
the if..then statement, but I can't seem to get it to work. Am I missing
something?

Thanks for your help.

Tim

"Tom Ogilvy" wrote:

Dim rw as Long, i as Long
rw = cells(rows.count,1).End(xlup).Row
for i = rw to 9 step -1
if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then
rows(rw).Delete
end if
Next

Hopefully your data actually contains numbers and not numbers stored as
strings.
--
Regards,
Tom Ogilvy


"TJN" wrote in message
...
I have a spreadsheet with amounts that start in column C and goes
through
column AE (columns A & B contain an entity number and an entity name).
The
number of rows varies, but it is typically about 600 rows of data.
This
data
begins on row 9. Row 8 contains the column headers and the 1st 7 rows
contain junk that comes over from the program I download this data
from.
It
can be eliminated if it helps.

Basically I am looking for a macro that steps through each row of data
and
if the sum of column C through column AE = zero, I would like the
entire
row
to be deleted.

Does anybody have any ideas? I feel bad because this is the third
time I
have come to this group for help. Each time the solutions have been
terriffic. I keep hoping I can answer a question instead of always
asking,
but so far all the questions asked have been above my skill level.

Thanks,

Tim

If sum = 0, delete row
 

Tom -

Thanks. The macro works great. I appreciate your help.

Tim

"Tom Ogilvy" wrote:

A couple of typos - try this which worked in my test:

Sub ABCD()
Dim rw As Long, i As Long
rw = Cells(Rows.Count, 1).End(xlUp).Row
For i = rw To 9 Step -1
If Application.Sum(Cells(i, 3).Resize(1, 29)) = 0 Then
Rows(i).Delete
End If
Next

End Sub

--
regards,
Tom Ogilvy

"TJN" wrote in message
...
Tom -
I punched this in and it deletes only the very last row of data (which
happens to sum to zero - I didn't test to see what happens if it doesn't
sum
to zero). I was messing around with an Offset command to move up a row
after
the if..then statement, but I can't seem to get it to work. Am I missing
something?

Thanks for your help.

Tim

"Tom Ogilvy" wrote:

Dim rw as Long, i as Long
rw = cells(rows.count,1).End(xlup).Row
for i = rw to 9 step -1
if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then
rows(rw).Delete
end if
Next

Hopefully your data actually contains numbers and not numbers stored as
strings.
--
Regards,
Tom Ogilvy


"TJN" wrote in message
...
I have a spreadsheet with amounts that start in column C and goes
through
column AE (columns A & B contain an entity number and an entity name).
The
number of rows varies, but it is typically about 600 rows of data.
This
data
begins on row 9. Row 8 contains the column headers and the 1st 7 rows
contain junk that comes over from the program I download this data
from.
It
can be eliminated if it helps.

Basically I am looking for a macro that steps through each row of data
and
if the sum of column C through column AE = zero, I would like the
entire
row
to be deleted.

Does anybody have any ideas? I feel bad because this is the third
time I
have come to this group for help. Each time the solutions have been
terriffic. I keep hoping I can answer a question instead of always
asking,
but so far all the questions asked have been above my skill level.

Thanks,

Tim

If sum = 0, delete row
 

Hi TJN,

I would like to know the step-by-step to use this macro as I need it too. Do
you mind telling me? Thanks, sorry for the trouble as I am total beginner in
macros.

Sharon

"TJN" wrote:

Tom -

Thanks. The macro works great. I appreciate your help.

Tim

"Tom Ogilvy" wrote:

A couple of typos - try this which worked in my test:

Sub ABCD()
Dim rw As Long, i As Long
rw = Cells(Rows.Count, 1).End(xlUp).Row
For i = rw To 9 Step -1
If Application.Sum(Cells(i, 3).Resize(1, 29)) = 0 Then
Rows(i).Delete
End If
Next

End Sub

--
regards,
Tom Ogilvy

"TJN" wrote in message
...
Tom -
I punched this in and it deletes only the very last row of data (which
happens to sum to zero - I didn't test to see what happens if it doesn't
sum
to zero). I was messing around with an Offset command to move up a row
after
the if..then statement, but I can't seem to get it to work. Am I missing
something?

Thanks for your help.

Tim

"Tom Ogilvy" wrote:

Dim rw as Long, i as Long
rw = cells(rows.count,1).End(xlup).Row
for i = rw to 9 step -1
if Application.Sum(Cells(rw,3).Resize(1,29)) = 0 then
rows(rw).Delete
end if
Next

Hopefully your data actually contains numbers and not numbers stored as
strings.
--
Regards,
Tom Ogilvy


"TJN" wrote in message
...
I have a spreadsheet with amounts that start in column C and goes
through
column AE (columns A & B contain an entity number and an entity name).
The
number of rows varies, but it is typically about 600 rows of data.
This
data
begins on row 9. Row 8 contains the column headers and the 1st 7 rows
contain junk that comes over from the program I download this data
from.
It
can be eliminated if it helps.

Basically I am looking for a macro that steps through each row of data
and
if the sum of column C through column AE = zero, I would like the
entire
row
to be deleted.

Does anybody have any ideas? I feel bad because this is the third
time I
have come to this group for help. Each time the solutions have been
terriffic. I keep hoping I can answer a question instead of always
asking,
but so far all the questions asked have been above my skill level.

Thanks,

Tim