Deleting Rows in Excel VBA

Question

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.

You could always filter the 0's in the column you want and then delete the filtered rows — Splendonia
– Splendonia, Commented Jul 19, 2016 at 19:17

Absinthe · Accepted Answer · 2016-07-19 18:25:08Z

1

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

answered Jul 19, 2016 at 18:25

Absinthe

3,4218 gold badges37 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Deleting Rows in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related