jeudi 2 mars 2017

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:

  • 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.

Aucun commentaire:

Enregistrer un commentaire