CODEWITHSUNDEEP
vbaexcel
Cassidy
Cassidy

Reputation: 9

Excel VBA - Why is my code not ending?

    Sub DeleteDetails()
    For Row = 3 To 50
        If Cells(Row, "B").Value = "" Then
         Cells(Row, "B").EntireRow.Delete Shift:=xlUp
         Row = Row - 1
        End If
    Next Row
End Sub

The code successfully does what I want it to do, but I eventually have to hit escape to stop it from running. I thought the "From Row 3 to 50" would stop it once it got to Row 50...

Upvotes: 1

Views: 227

Answers (2)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

Your issue is your loop is trying to move sequentially forward from 3 to 50, unless the value in B is blank, then you are deleting the row and "re-running" that row number. If your hitting the row after the last row with data in it, you will continuously run that row number. you may need an exit to check for that like below:

Sub DeleteDetails()
    For Row = 3 To 50
        If Cells(Row, "B").Value = "" Then
         Cells(Row, "B").EntireRow.Delete Shift:=xlUp
         Row = Row - 1
            If Cells(Row,"A").value = "" then
                'assuming A will have data if the row isn't empty
                Exit Sub
            End If
        End If
    Next Row
End Sub

or you can step backwards through your range by finding the last row and then stepping backwards like with something like 

For Row = iLastRow to 3 Step -1
    'Do stuff here
Next Row

Stepping backwards would be the better approach.

Upvotes: 2

u8it
u8it

Reputation: 4296

If the last row is blank, then the loop gets stuck in a continuous loop deleting row 50 until row 50 column B has a value, if there are no more values past row 50 then the loop will be infinite. To demonstrate this place a value in row 100 column B and it will appear in row 50 (with the old rows 50-99 deleted). I think the best solution is to realize that it's always dangerous to modify your loop counter within its own loop, so simply separate your counter from your row pointer as the following code does:

Sub DeleteDetails()
    Row = 3
    For i = 3 To 50
        If Cells(Row, "B").Value = "" Then
            Cells(Row, "B").EntireRow.Delete Shift:=xlUp
        Else
            Row = Row + 1
        End If
    Next i
End Sub

Upvotes: 0

Related Questions