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!

--
Ann Scharpf


"Otto Moehrbach" wrote:

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" wrote in message
...
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




--
Ann Scharpf