CODEWITHSUNDEEP
excelvbarows
FMorelli
FMorelli

Reputation: 13

Deleting Rows in Excel VBA

The Following exemplifies the code I am trying to run

For i = 5 To LastRow

Cells(i, 13).Select

    var2 = Cells(i, 13)

    If var2 = 0 Then

    Rows(i).Delete

    Else

    var3 = 1

    End If

Next i

Basically I have a column full of 1's and 0's. If the cell contains a zero I need for the column to be deleted. My problem comes when adjacent cells contain zeros. The code will delete the cell, the proceeding row with the zero will shift up and the loop will move on to the next row, missing the zero that was shifted up.

Anyone have a solution for this.

Upvotes: 0

Views: 104

Answers (1)

Absinthe
Absinthe

Reputation: 3391

Rows is a collection so when you iterate through it and delete part of the collection the upper bound of the collection is reduced. Since the loop started it expectes the collection to always be the same size.

Long story short: loop backwards through the collection, then you'll always be in the same relative place even if you delete something from it:

'For i = LastRow To 5 step - 1

'Cells(i, 13).Select

' var2 = Cells(i, 13)

' If var2 = 0 Then

' Rows(i).Delete

' Else

' var3 = 1

' End If

'Next i

Upvotes: 1

Related Questions