if function to delete row

[AskExcel]

hi,
may i know how to write this?

if column X or column y or column z is 0 then i need to delete this row.

thanks

 

[Mike H]

Right click you sheet tab, view code and paste this in and run it

Sub marine()
Dim myrange, copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row
Set myrange = Range("X1:X" & Lastrow)
For Each c In myrange
If Not IsEmpty(c) And c.Value = 0 And _
Not IsEmpty(c.Offset(, 1)) And c.Offset(, 1).Value = 0 And _
Not IsEmpty(c.Offset(, 2)) And c.Offset(, 2).Value = 0 Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike

 

[AskExcel]

Hi,
thanks but i do not understand your code.
may i know if there is any simpler way?
thanks

 

[Mike H]

Hi,

What you can't do is have an IF formula along the lines of

=If(x2=0,delete the row, don't delete the row)
so if you want to delete rows without code then it's a manual process. You
could set up an autofilter to only display the rows that meet your criteria
and then delete the visible rows but why not try the code it's simple

Right click you sheet tab, view code and paste it in and run it by tapping F5

Mike

 

[Dave]

Hi AE,

As far as I am aware, a formula can only give you a result, not an action.
So a formula cannot delete rows; you have to use a macro.
Regards - Dave.