Delete all Rows in a Variable Range
 

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

Delete all Rows in a Variable Range
 

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

-----Original Message-----
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


.

Delete all Rows in a Variable Range
 

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




"steve" wrote in message
...
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

-----Original Message-----
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


.

Delete all Rows in a Variable Range
 

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

"John" wrote in message
...
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




"steve" wrote in message
...
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

-----Original Message-----
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


.