Do While ....Loop Never Stops

[jadown]

I used this loop on another spreadsheet and just reused it on a new one but the loop gets stuck and keeps running. What do you see wrong with this Do While....Loop?

[tigeravatar]

Isn't that code basically used to delete all rows below A1? Instead of a loop, why not just have a simple range.clear?

Please Login or Register  to view this content.

[jadown]

tigeravatar,

The sheet has multiple lines through the sheet that has the column titles throughout the sheet which has a empty row before them and after them. So I used a similar loop on another sheet to do the same thing and it worked. But on this new sheet I modified the macro a little and it deleted everything on the sheet.

I will give your suggestion a try to see if it works. Thanks for the help.

[Debraj Roy]

Lemme try to clear..

You cant delete No Of Rows.. It will always be 65536 (yes I know.. 2^20 also).. so whenever, you are re-checking .. on next step.. i will still be 65536.
So obviously loop will run forever..

Try This.

Please Login or Register  to view this content.

[JosephP]

your problem:
1 only if A2 is empty or column A is fully populated will Range("A1").End(xlDown).Row reach the last row and your loop will exit
2 if A2 is not empty, you find the next blank row and delete 4 rows then repeat but then option 1 can never be true and your loop cannot exit

[jadown]

Josie & Debraj,

Thanks for your help. It works now.
I think I understand your code Debra. I'll have to look at it a couple more times to understand how you came up with it because I would've never thought of that.
Thanks again!