automating delection of certain rows

[sam]

I am trying to automate through a Macro the operation
where all rows in a worksheet with a value of 0 (zero)
will be deleted. how may I accomplish this?

 

[Gjones]

Hi Sam;

This code assumes that every cell starting in cell A1 and
going down has something in it for every line and then
deletes the row if you want to check the cells in column D

Sub DeleteTheZeros()
Range("A1").Select
While Not ActiveCell = ""


'checks for the zero value 3 columns to the right
of column A
If ActiveCell.Offset(0, 3) = 0 Then
Application.DisplayAlerts = False
ActiveCell.EntireRow.Delete
End If

ActiveCell.Offset(1, 0).Select
Wend
End Sub

Thanks,

Greg

 

[Tom Ogilvy]

Select a single set of cells, 1 column wide. then run this macro.

Sub DeleteRows()
Dim i as Long
for i = selection.rows(selection.rows.count).row to selection.Row step -1
if cells(i,Selection.Column).Value = 0 and
Trim(Cells(i,Selection.Column).Value) <> "" then
cells(i.Selection.Column).Entirerow.Delete
end if
Next
End Sub

 

[Guest]

Thank you for your help. When I try to run this Macro, I
get an error message. End If is highlighted and the error
message box reads: End If Without Block If

how may I fix this?

sam

 

[JWolf]

Check your typing or line breaks from newsreader, it works fine for me.

 

[Tom Ogilvy]

Sub DeleteTheZeros()
Range("I2").Select
While Not ActiveCell = ""

If ActiveCell = 0 Then
Application.DisplayAlerts = False
ActiveCell.EntireRow.Delete
else
ActiveCell.Offset(1, 0).Select
End if
Wend
End Sub


--

Regards,
Tom Ogilvy

 

[Tom Ogilvy]

If you have two rows in succession that have zeros in them, it will miss the
second row

so I am not sure that qualifies as working fine - although it does execute -
a less strenuous interpretation.