Problem with Do Until a certain row

[rabbithk]

Hi everyone,

I have been trying to write a macro on deleting the rows with column B cells empty, that is deleting B3 and B4 in the data below. I used an If and Else statements and loop them until B7.

A	1
B	2
C
D
E	3
F	4

<tbody>
</tbody>

My code is as follows:

Sub proDelete()

    Range("b1").Select
    Do Until ActiveCell.Row = 7
            If Selection.Value = "" Then
                Selection.EntireRow.Delete
            Else
                Selection.Offset(1, 0).Select
            End If
    Loop


    Range("A1").Select
            
End Sub

However, while running the macro, my excel freezes with the word [running] on top of the VBA editor. Is there any problem with my code?

Thanks!

 

[Special-K99]

Could it possibly be this?

You have 7 rows with blanks on rows 3 and 4
If the row is blank it deletes it otherwise moves down
Everything is fine until
It reaches row 3 deletes it, everything shifts up, you now have 1,2, ,3,4
It's still on row 3, it deletes it, everything shifts up, you now have 1,2,3,4
Row 3 now contains 3 so it moves down 1 row
Row 4 contains 4 so it moves down to row 5
However...

Row 5 is blank, it deletes it, but from this point on everything onwards is also blank
so the active cell pointer will not move down any further.
It will therefore never reach row 7, so the loop wont end hence the freeze.

 

[rabbithk]

Oh yes thank you for reminding. I just realized that. Thanks a lot!

Could it possibly be this?

You have 7 rows with blanks on rows 3 and 4
If the row is blank it deletes it otherwise moves down
Everything is fine until
It reaches row 3 deletes it, everything shifts up, you now have 1,2, ,3,4
It's still on row 3, it deletes it, everything shifts up, you now have 1,2,3,4
Row 3 now contains 3 so it moves down 1 row
Row 4 contains 4 so it moves down to row 5
However...

Row 5 is blank, it deletes it, but from this point on everything onwards is also blank
so the active cell pointer will not move down any further.
It will therefore never reach row 7, so the loop wont end hence the freeze.

 

[farmerscott]

If deleting rows it is always best to start from the bottom and work your way up.

Eg for x= lastrow to 1 step -1

This gets around all the issues that Special-K99 highlighted.