delete zero value's using a macro
 

I have a formula to delete cells that have a zero value which is shown
below

Dim cell As Range

For Each cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
If cell.Value = 0 Then cell.Delete
Next cell

That works fine but im trying to delete the whole line and not just the
cells that have zero in them.
I have a workbook which is set up as follows

outstanding paid paid paid outstanding


item a 300.00 100.00 200.00 0.00
item b 40.00 5.00 35.00
Item c 33.00 8.00 25.00

Im trying to delete the whole line if the outstanding amount shown at
the end of the line is zero. i.e the top line (the line horizontally
from item a to 0.00)

Would I be able to set it into a macro that deletes the zero amounts
first and then transfer all the other data which has amount other than
zero into a new workbook.


--
tweacle

delete zero value's using a macro
 

One way:

Maybe something like:

Option Explicit
Sub testme()

Dim rng As Range
Dim wks As Worksheet
Dim myCell As Range
Dim DelRng As Range

Set wks = Worksheets("Sheet1")

With wks
Set rng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
End With

For Each myCell In rng.Cells
If IsEmpty(myCell) = False Then
If myCell.Value = 0 Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
DelRng.Select
'or (after testing!)
'DelRng.EntireRow.Delete
End If

End Sub

And after you've deleted the rows with 0's in column D, you can do whatever you
want with the other data.

tweacle wrote:

I have a formula to delete cells that have a zero value which is shown
below

Dim cell As Range

For Each cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
If cell.Value = 0 Then cell.Delete
Next cell

That works fine but im trying to delete the whole line and not just the
cells that have zero in them.
I have a workbook which is set up as follows

outstanding paid paid paid outstanding

item a 300.00 100.00 200.00 0.00
item b 40.00 5.00 35.00
Item c 33.00 8.00 25.00

Im trying to delete the whole line if the outstanding amount shown at
the end of the line is zero. i.e the top line (the line horizontally
from item a to 0.00)

Would I be able to set it into a macro that deletes the zero amounts
first and then transfer all the other data which has amount other than
zero into a new workbook.

--
tweacle

--

Dave Peterson