If sum = 0, delete row

[TJN]

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

[Tom Ogilvy]

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" <TJN@discussions.microsoft.com> wrote in message
news:3F7C72BD-C725-48D9-B7CE-EB255C9B539A@microsoft.com...
> 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

[TJN]

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" <TJN@discussions.microsoft.com> wrote in message
> news:3F7C72BD-C725-48D9-B7CE-EB255C9B539A@microsoft.com...
> > 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
>
>
>

[Tom Ogilvy]

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" <TJN@discussions.microsoft.com> wrote in message
news:5C3338E9-2B7D-4F0C-8712-F3C18660738A@microsoft.com...
> 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" <TJN@discussions.microsoft.com> wrote in message
> > news:3F7C72BD-C725-48D9-B7CE-EB255C9B539A@microsoft.com...
> > > 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
> >
> >
> >

[TJN]

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" <TJN@discussions.microsoft.com> wrote in message
> news:5C3338E9-2B7D-4F0C-8712-F3C18660738A@microsoft.com...
> > 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" <TJN@discussions.microsoft.com> wrote in message
> > > news:3F7C72BD-C725-48D9-B7CE-EB255C9B539A@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>