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:
- Checks line 14
- Deletes line 14 because blank
- Line 15 shifts up to become the new line 14
- For loop moves on to Line 15, missing the new line 14
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.
2 Answers
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 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.
5
