Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a range of rows to delete | Excel Worksheet Functions | |||
Deleting a range of rows based on a variable; syntax error | Excel Discussion (Misc queries) | |||
Variable criteria to delete rows | Excel Programming | |||
delete rows in range - macro | Excel Worksheet Functions | |||
How to copy&paste a variable range rows and colums | Excel Discussion (Misc queries) |