Delete all Rows in a Variable Range

[John]

How can I delete all Rows in a variable range where the value in Column J
returns Zero and move all Rows below up. Column J contains a formula
relating to other values in the same row.

Thanks

 

[steve]

here is two posibilitys

Sub killme()
Dim r As Range
For Each r In Selection
If r.Column = 10 And r.Value = 0 Then
r.EntireRow.Delete
Next
End Sub

or

Sub killme()
Dim r As Range
For Each r In Selection
If Cells(r.Row, 10) = 0 Then r.EntireRow.Delete
Next
End Sub

 

[John]

Thanks Steve, one small error appearing, I've added to it just a small bit
but I'm stuck after "Next" - with error Next without For

Sub DeleteZerosinIngredients()

With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim r As Range
For Each r In Selection

Sheets("Ingredient Mix").Select
With ActiveSheet
If Cells(r.Row, 10) = 0 Then r.EntireRow.Delete
Next

End With
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

 

[Cecilkumara Fernando]

John,
Sheets("Ingredient Mix").Select
'I think you have to make a range selection
'Sheets("Ingredient Mix").Range("A1:A1000").Select

For Each r In Selection
With ActiveSheet
If Cells(r.Row, 10) = 0 Then r.EntireRow.Delete
End With
Next