Unless you have data in colums to the right of your range you can delete the entire row. By deleting the entire row you eliminate having to move following rows up to fill the blank deleted range. Deleting rows changes the bounds of your used range which can be a problem when done in a For Loop. To avoid this problem you can 'Step -1' to loop from the last row of your range to the first. This avoids the row number sequence problem caused by deleting a rows within a For Loop.
If you have data to the right of your data area you can use Range Delete shift XL Up to delete a specified area.
In this example I simplified the process by using 'With Activesheet' and '.Cells(r,c)' I just added the values of Cells in colums C,D,E to check the criteria, easy enough assuming you will always be checking for numeric values in these cells.
Code:
Sub eleminate1()
Dim irowcount As Integer
Dim r As Integer
Dim i As Integer
Dim lngAddCDE As Long
' Sheet4.Activate 'Activate sheet if not called from the active sheet
With ActiveSheet
irowcount = .UsedRange.Rows.Count
For r = irowcount To 1 Step -1
lngAddCDE = 0
For i = 3 To 6
If IsNumeric(.Cells(r, i)) Then
lngAddCDE = lngAddCDE + .Cells(r, i)
End If
Next i
If lngAddCDE = 0 Then
.Rows(r).EntireRow.Delete
End If
Next
End With
End Sub
dBforums
>
moving a row upward in excel
08-13-04, 14:58
Linear Mode
