How to delete entire row based if amount in zero

[EagerToLearn]

Hello there
I have been asking questions and never turned down, all answers I got back were perfectly correct and served my purpose, so as usual thanks in advance
I have a simple question
In excel sheet that contains data in column A:H with column E for amount
How can I delete the entire row if the dollar amount under column E is zero
Your help is much appreciated

 

[Snakehips]

Try something like this....

Sub DeleteZero()

Col = 5 'Column E
With ActiveSheet  ' or Sheets("Your Sheet Name")
Application.ScreenUpdating = False

  For r = 1 to 10  'Set your own limits for r
      If .Cells (r,Col).Value = 0 Then .Cells(r,Col).EntireRow.Delete 
  Next r
Application.ScreenUpdating = True

End With

End Sub

 

[AlphaFrog]

• Select column E
• Select from the menu Data\Filter\Autofilter
• This will put a dropdown list in cell E1
• Select from the E1 dropdownlist either zero or "Blanks"
• This will hide all the rows that are not zero or Blank
• Select the visible rows and delete
• Select Autofilter again to turn it off

 

[EagerToLearn]

Hello Alfa,
Thanks a lot for your kind reply, I was looking for VBA code.
Your trick will do and really good help.
Appreciated.

 

[Rick Rothstein]

I was looking for VBA code.

If that is still the case and if your values in Column E are constants (that is, there are no formulas in Column E), then you can use this quick code to delete the indicated rows...

Sub DeleteRowsForZeroesInColumnE()
  Columns("E").Replace 0, "=0", xlWhole
  On Error Resume Next
  Columns("E").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
End Sub

If the values in Column E are not constants (that is, they are formulas, then tell me the row number the formulas start on and I'll adjust the code to handle that situation for you.

 

[EagerToLearn]

Hello Snakehips,
Very good simple code, but I am afraid I might be doing something wrong.
It not deleting every row that have zero on column E.
It is deleting some and letting other without deletion.
I can not tell why.
May be formatting issue.
Like it is not seeing 0.00 as zero dollar
Advise?

 

[AlphaFrog]

If you're using Snakhips code, change this...

For r = 1 to 10  'Set your own limits for r

To this

For r = 10 to 1 Step -1  'Set your own limits for r

You need to delete from the bottom up to prevent skipping over consecutive zeros

 

[EagerToLearn]

Hello Rick,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Hope the storm did not really hit you bad; I lived in NY couple years and visited <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Jersey</st1lace> couple times<o></o>
The code you sent is a master piece, it is working just great.<o></o>
Thanks and have a nice weekend<o></o>
<o> </o>

 

[EagerToLearn]

Hello Alfa,
Got it, it is working now, thanks for the time you spent on that.
I am working hard on a big book I bought for VBA programming.
Studying slowly but surely on the right track.
Thanks all for your kind help

 

[Rick Rothstein]

@EagerToLearn

First off, you are quite welcome for the code... I'm glad it worked out for you. Second, I live in central NJ and we actually did okay here for the main part. We have a major creek here that set an all-time flood stage record and there were many people who live in its flood plain affected by flooding from it, but we were warned to expect all kinds of havoc from downed trees because of already overly saturated ground around tree roots, but when the storm got to NJ, the winds actually lessened and we were spared that part of the scenario. Unfortunately, those living in northeast NJ got hit quite hard from river flooding (it made nation news here), so I really feel for them though.