Do While Loop Does not Stop

Question

I created this macro to delete rows with a 0 value on Column 5, starting on Row6. It should stop on the Row with an empty cell on Column 2. My data has about 600 rows only. However, after Row 20 or so, the spreadsheet just kept waiting and I need to press Esc Key to stop it. When it stops, it is at the last row but it does not do the last part that is to sum column 4.

If I press the macro again, it works fine with the new data and adds the sum in column 4. (by the way, it is my first macro in excel).

Sub DeleteRows()
'
' DeleteRows Macro
' Keyboard Shortcut: Ctrl+d
'

    Cells(6, 5).Select
    Do While (Cells(ActiveCell.Row, 2) <> "")
        Do While (Cells(ActiveCell.Row, 5) = 0)
            Rows(ActiveCell.Row).Select
            Selection.Delete Shift:=xlUp
        Loop
        Cells(ActiveCell.Row + 1, 5).Select
    Loop

    Cells(ActiveCell.Row, 4).Select
    Dim cel1 As String, cel2 As String
    cel1 = ActiveCell.Offset(-1, 0).End(xlUp).Address
    cel2 = ActiveCell.Offset(-1).Address
    ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"

End Sub

score 1 · Accepted Answer · 2018-02-17T05:53:09.430

1

Loop from the bottom up when deleting rows or you risk skipping over rows.

sub deleteRows()
    dim i as long
    with worksheets("Sheet1")
        for i=.cells(.rows.count, "B").end(xlup).row to 6 step -1
            if .cells(i, "E").value2 =0 then
                .cells(i, "E").entirerow.delete
            end if
        next i
        with .cells(.rows.count, "D").end(xlup)
            .offset(1, 0).formula = "=sum(" & .parent.range(.cells(1), .cells(1).end(xlup)).address & ")"
        end with
    end with

end sub

Indent your code.
How to avoid using Select in Excel VBA
Define a parent worksheet reference for all cells and range calls.

edited Feb 17 '18 at 05:53

answered Feb 17 '18 at 05:39

It worked beautifully (fast, clean), thank you very much. – Fabimar Feb 17 '18 at 06:15
I thought I had it right the first time around but after several minutes I realized that I had to escape the nested With statement by one level with .Parent. – Feb 17 '18 at 07:54

Do While Loop Does not Stop

1 Answers1