For next loop to delete empty row (advice my code)

Hi, i tried using for next loop to delete empty row. My code is as below

Sub del()
Dim lr As Long, i As Integer
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lr

If Cells(i, 1) = "" Then
Rows(i).Delete
i = i - 1
End If
Next i
End Sub

whenever i trigger the marco. My excel is like "hang". However, when i keep pressing ESC. It prompt the message saying the macro is being interupt. HOwever, then all my empty row has been deleted. What is wrong with my code?

Hello JP16,

To delete rows using a loop you need to work from the bottom up
Also, remove the counter (i = i - 1) as it is contradicting the Next i function.

So your code should look like this:-

Sub del()

Dim lr As Long, i As Integer
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 1 Step -1 '--------> From the lr up to the top. Step -1 will "catch" the next _
                                   row with a blank cell in Column A as the rows move up when deleted.
     If Cells(i, 1) = "" Then
     Rows(i).Delete
     End If
Next i

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Ooops! Sorry Jeff. I've done it again! Please remove my post as its the same reply as yours.

Cheerio,
vcoolio.

Originally Posted by vcoolio

Ooops! Sorry Jeff. I've done it again! Please remove my post as its the same reply as yours.

No reason to remove it, and we don't remove posts just because they recommend the same solution. Even if it is the same solution it is good to have explanations from different angles. Great minds think alike

No worries Jeff. Its hard to tell who else is working on the same thread until post time.

Thanks again.

vcoolio.

Hi, please see attached. It will hang but eventually work if i press "ESC" to abort the code. why???
So if i wan I to decrease, i have to add a Step -1?

Anway, i thought my code consist of the i=i-1 which should address the problem u mention regarding deleting row?

Originally Posted by jp16

Hi, please see attached. It will hang but eventually work if i press "ESC" to abort the code. why???
So if i wan I to decrease, i have to add a Step -1?

Anway, i thought my code consist of the i=i-1 which should address the problem u mention regarding deleting row?

As I said, changing the index value inside a For loop is a poor programming practice. Did you try my version of the code?

The problem with your code is that you start with i=1, then with "i=i-1" i is now 0.
Then when the loop repeats with next i, the loop starts again with i=1, hence a never ending loop process.

Ya, i thot so too. But somehow, when i press ESC to abort. The end result is correct... funny....

I just noticed it, because the i=i-1 is in the if loop instead of the for loop, variable i will not -1 if the cell value is not blank. (you can see that for Cell(1,1) there is a value so it did not -1). Hence it will eventually get your answer but it will still stuck in a never ending loop

Ya, i tried, work perfect! Thanks! Anyway, does the "step -1" mean the "i" decrease by 1 everytime right? Then can we make it step +2 to make "i" increase by 2 everytime?