Code to delete rows where cell value is zero

[Ann Scharpf]

I am working in an Excel sheet where data is dumped from another system.
Many of the rows are extraneous - if the cell in the Current Hours column
(AB) contains a zero value, I'd like my macro to delete the row. Someone
here at my office knows more VBA than I do (which is very little) and we came
up with the following. It is NOT working correctly.

The current sheet of test data contains 575 rows. Of these, 24 have values
greater than zero in column AB. 551 rows contain a zero in AB. When I run
the macro, it does not seem to actually be using the values in AB to
determine what to delete. Depending on which cell is active when I start,
sometimes there are values > 0 in AB, other times they are all = 0. I end up
with a different number of rows depending on where I start.

Here is the code of the macro. If anyone can tell me how to make the macro
look at the contents of the cells in column AB and delete when they contain
0, I would really appreciate it.

Thanks very much for your help.

Sub DelBlankCurHrsRows()
'
' DelBlankCurHrsRows Macro
' Macro recorded 10/18/2006 by Ann Scharpf
'
' Keyboard Shortcut: Ctrl+Shift+E
'
For Each c In Worksheets("Paste all employees' data
here").Range("AB2:AB41000")
If c.Value = "0" Then
Selection.EntireRow.Delete
End If
Next
End Sub

 

[Otto Moehrbach]

Ann
When you are looping through a range and deleting rows, you want to be
sure you loop from the bottom up rather than from the top down. This macro
will do what you want, but your "blank" cells in Column AB must be actually
blank. I know you say Huh? at this point but when data is brought into
Excel from an external source, problems may come with the data. For
instance, a "blank" cell may have a space in it. That cell is not blank.
It is an occupied cell. I included code in this macro to Trim (remove all
extraneous spaces) all the cells in Column AB. Come back if this macro does
not delete ALL the "blank" cells. HTH Otto
Sub RemoveBlank()
Dim RngColAB As Range
Dim c As Long
Application.ScreenUpdating = False
Set RngColAB = Range("AB2", Range("AB" & Rows.Count).End(xlUp))
For c = RngColAB.Count To 1 Step -1
RngColAB(c).Value = Application.Trim(RngColAB(c))
If IsEmpty(RngColAB(c).Value) Then _
RngColAB(c).EntireRow.Delete
Next c
Application.ScreenUpdating = True
End Sub

 

[Ann Scharpf]

Hi, Otto:

Thanks for taking the time to try to help me.

I always end up causing confusion when I try to "alter the facts" for
brevity. Actually, this data is coming from other Excel sheets. We have 100
employees who fill out bi-weekly status reports. I have written macros to
copy the region where an employee MIGHT have entered data (never know how
many rows the employee will use week to week) and dump the data (Paste as
values) into an aggregate sheet. Further macros are eliminating columns that
are no longer needed and I would like to delete these rows that calculated
zero totals for the cumulative hours. The rows that I want to delete really
DO contain a zero in the Cumulative Hours cell.

I did add your code into my macros. When I run RemoveBlank, it doesn't seem
to do anything at all. Was I supposed to add something before or after this
code?

Thanks again.

Ann

 

[Ann Scharpf]

I reread your message and changed the code as follows:

If IsEmpty(RngColAB(c).Value) Then _
changed to
If RngColAB(c).Value = 0 Then _

This made the macro work. It deletes all but the 24 rows that have a value

0. Thanks very much for your help. Now I will READ the code and try to

understand it!

 

[Cheekyaardvark]

Otto...
Not sure if I posted this correctly last time.. but I was wanting to
take the macro here one step further & have it so that if I highlight
any column in any spreadsheet that it would remove any rows with zero
value in that column... I had macros before that simply utilised
autofilter but with Excel 2003 when using large files excel just seems
to hang

Can you give me some guidance..
Much Appreciated

Robin

 

[Otto Moehrbach]

Robin
By "remove" do you mean hide or delete the row? If a row is deleted
it's gone forever once the file is saved. If the row is hidden, it can be
simply un-hidden later and it's back. Start a new post or contact me
direct. My Outlook Express is listing your posts back with the thread in
October and I'm losing you. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto