Why does my loop not stop?

[ellen01]

Hello there,

I ran the code before on a spreadsheet with a number of lines. I basically want to remove any lines with the value in column C starting with 'M'. As there wouldn't be two blank rows in a row anywhere on the sheet apart from at the end of the filled range, I used the loop conditions below. So when there are two blank rows in a row, the loop should stop. However, for some reason this loop does not stop but keeps giving me an 'overflow' error after deleting the contents as desired.

P.S. I do not know how to attach files here so cannot provide the test data I'm using to run the code sorry..... Can anyone spot any problems in my code?

Thanks a lot in advance!

Ellen

Sub DeleteModel()


Dim i As Integer


i = 2


Sheets("sheet1").Select


Do While Cells(i, 3).Value <> "" & Cells(i + 1, 3).Value <> ""
   
    If Cells(i, 3).Value Like "M*" Then
        Call DeleteRow(i)
        If Cells(i, 3).Value = "" Then
        Call DeleteRow(i)
        End If
    Else
        i = i + 1
    End If
    
Loop


End Sub

 

[mumps]

You can't attach files in this forum. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Fluff]

Try

Do While Cells(i, 3).Value <> "" And Cells(i + 1, 3).Value <> ""

 

[ellen01]

Thanks Fluff, but I have tried that already. What happens is that the code just doesn't run at all. No errors but just doesn't do anything. Very strange because you'd think using 'And' would do at least the same thing as using '&'.

 

[ellen01]

Thank you! I will try this.

You can't attach files in this forum. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Joe4]

Did you try stepping through your code line-by-line to see/follow exactly what it is doing (by stepping into code and using F8 to go through one line at a time)?

 

[Fluff]

Very strange because you'd think using 'And' would do at least the same thing as using '&'.

Nope. & is used to concatenate, which is not the same as And (a logical operator)

 

[ellen01]

Hello all,

Here is the link to a spreadsheet with generic data. https://www.dropbox.com/s/kveyffskjb4vwjp/Example.xlsx?dl=0

What I am trying to do is to delete the lines with a value in column C that starts with M. Also as you can see, there is a blank row between blocks of rows with the same value in column A. I want to keep it that way, so when row 11 is removed, the blank row underneath it should be removed too, so that there won't be two blank rows between the blocks of data. Hope this makes sense.

Thank you! I will try this.

 

[ellen01]

Yes I tried that, and I find that whilst it deletes the rows as desired, the loop carries on running for ever.

Did you try stepping through your code line-by-line to see/follow exactly what it is doing (by stepping into code and using F8 to go through one line at a time)?

 

[ellen01]

Hmmm then it is very strange as to why '&' here works but 'And' does not work at all.

Nope. & is used to concatenate, which is not the same as And (a logical operator)