View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Ann Scharpf Ann Scharpf is offline
external usenet poster
 
Posts: 23
Default Code to delete rows where cell value is zero

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


"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