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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
813
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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