CODEWITHSUNDEEP
excelvbaif-statementfor-loop
LWrig
LWrig

Reputation: 33

VBA - Decrement a for loop within an if

I'm running a macro to clean up empty lines from a workbook, and due to how the For loop runs, it'll miss consecutive empty lines because it, for example:

My (possibly flawed) solution was to add a step after deleting the row which decrements the for loop by one step, so that it'll catch consecutives.

I tried:

For i = 1 To 2000
    If IsEmpty(Cells(i, 6)) = True Then
        Rows(i).EntireRow.Delete
        i = i - 1
    End If
Next i

Which seemed to loop infinitely. I also tried i -= 1, --i, etc but those fail due to an expected expression. i = --i atleast runs but doesn't seem to work in so far as removing blank lines.

Any advice? Apologies in advance if missed something obvious.

Upvotes: 3

Views: 14066

Answers (2)

Brian Cryer
Brian Cryer

Reputation: 2226

Try running it in reverse, so starting at 2000 (using your example, or I think Worksheet.Rows.Count) and working back down to 1. That way you won't need to fiddle with your loop index when you delete a row.

So:

For i = 2000 to 1 step -1
    If IsEmpty(Cells(i, 6)) = True Then
        Rows(i).EntireRow.Delete
    End If
Next i

The reason why your current code loops is that line 2000 is blank, so it deletes it and sets your loop count back to 1999, so it then tries line 2000 again which is blank and so on.

Upvotes: 1

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

You should really step backwards when deleting, so start at 2000 and work your way down to 1 to ensure no lines are skipped.

For i = 2000 To 1 step -1
    If IsEmpty(Cells(i, 6)) = True Then
        Rows(i).EntireRow.Delete
    End If
Next i

Upvotes: 9

Related Questions