Creating a Loop for a static macro

[Alroj]

Hello,

I am having a bit of trouble with the VBA I have set up and wondering if some of you gurus can help me out please.

Column "L" contains the summation of the previous 5 columns and I want to delete the rows between columns A through to L if the sumation is zero (columns A through to E are text). So I have this macro that does the trick but only 1 range at the time. Also, when I put a loop I got the run time error. Could anyone pls help me to loop through many rows (variable range around 1000 rows). The current macro is:

Sub Macro1()



    Range("L6").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    
    lastrow = Cells(Cells.Rows.count, "a").End(xlUp).Row
    Range("l6:l" & lastrow).FillDown
  
    Calculate


         lastrow = Cells(Cells.Rows.count, "a").End(xlUp).Row
        Range("l6:l" & lastrow).Select
        
        Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Select
 
        Range(Selection, Selection.Offset(0, -11)).Select
        Selection.Delete Shift:=xlUp






End Sub

Thank you !!!

 

[Norie]

Does this work?

Sub Macro1()
Dim lastrow As Long
Dim I As Long

    Range("L6").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
    
    lastrow = Cells(Cells.Rows.count, "a").End(xlUp).Row
    Range("l6:l" & lastrow).FillDown
  
    Calculate

    For I = lastrow To 6 Step -1
        If Range("L" & I).Value = 0 Then
            Range("A" & I).Resize(,5).Delete Shift:=xlUp
        End If
    Next I 
       
End Sub

 

[Alroj]

Norie,

You are a legend. Thank you for your assistance.
It worked after I did the adjustment in the rezise section. So instead of Resize(, 5), it should be Resize(, 11), this way it deletes the rows (including text and numbers) that add up to zero. The final macro is:


Sub Macro1()
Dim lastrow As Long
Dim I As Long


Range("L6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"

lastrow = Cells(Cells.Rows.count, "a").End(xlUp).Row
Range("l6:l" & lastrow).FillDown

Calculate


For I = lastrow To 6 Step -1
If Range("L" & I).Value = 0 Then
Range("A" & I).Resize(, 11).Delete Shift:=xlUp
End If
Next I

End Sub