Delete a row if it contains only zeros - automatically?

T

turner2000

Hey,

Is it possible to delete an entire row if it contains only zeros s
that the row underneath moves up. I would like this to be able t
happen automatically or at the click of one button.

Is this possible?

Thanks
-turner200
 
B

BC...

Here is a script I've used for this purpose.

Sub RemoveZeroRows()
Application.ScreenUpdating = False
Dim X As Integer
X = CInt(InputBox("Enter number of rows to check for zero
values.", "Remove Zero Rows", 100))

For COUNTER = 1 To X
If ActiveCell.Value = 0 Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Next COUNTER
End Sub
 
D

Dave Peterson

Sometimes it's a lot easier to start at the bottom up:

Option Explicit
Sub RemoveZeroRows2()
Application.ScreenUpdating = False
Dim rCtr As Long

With ActiveSheet
For rCtr = .Cells(.Rows.Count, "A").End(xlUp).Row To 1 Step -1
If .Cells(rCtr, "A") = 0 _
And IsEmpty(.Cells(rCtr, "A")) = False Then
.Rows(rCtr).Delete
End If
Next rCtr
End With

Application.ScreenUpdating = True
End Sub

But if you want to start at the top down, you can build your range and delete it
all at once.

Option Explicit
Sub RemoveZeroRows3()
Application.ScreenUpdating = False
Dim delRng As Range
Dim myCell As Range
With ActiveSheet
For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
If myCell.Value = 0 _
And IsEmpty(myCell) = False Then
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End If
Next myCell
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Delete
End If

Application.ScreenUpdating = True
End Sub

===
And just a word of warning: Excel sees empty cells as having a value of 0.
 
Top