Problem with Do Until a certain row

rabbithk

New Member
Joined
Jun 18, 2015
Messages
7
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.

A1
B2
C
D
E3
F4

<tbody>
</tbody>

My code is as follows:
Code:
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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.
 
Last edited:

rabbithk

New Member
Joined
Jun 18, 2015
Messages
7
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

Well-known Member
Joined
Jan 26, 2013
Messages
814
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,273
Messages
5,836,329
Members
430,421
Latest member
Natas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top