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
|