Automatic removal of rows

P

Peter

/ Hi,
If I have worksheet where I want to delete all the rows that have (say) 0 in column C is there an automatic / formula way to do it. I've tried filter and then cut and paste special to new worksheet. Is there a simpler / neater / formulaic way to do it?
I need to achieve this because I want to import the result into another application that will not accept 0's in key columns - without manual intervention.
Many thanks
Peter
 
J

Jason Morin

Try this macro:

Sub DeleteRows()
Dim iLastRow As Integer
Dim i As Integer

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
With Cells(i, "C")
If .Value = 0 Then
.EntireRow.Delete
End If
End With
Next i

Application.ScreenUpdating = True

End Sub

--

Press ALT+F11, Insert > Module, and paste in the above
code. Then flip back to XL and run the macro.

HTH
Jason
Atlanta, GA
-----Original Message-----
/ Hi,
If I have worksheet where I want to delete all the rows
Click to expand...
that have (say) 0 in column C is there an automatic /
formula way to do it. I've tried filter and then cut and
paste special to new worksheet. Is there a simpler /
neater / formulaic way to do it?
I need to achieve this because I want to import the
Click to expand...
result into another application that will not accept 0's
in key columns - without manual intervention.
 
Top