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

 

[MARK858]

Hello all,

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

I don't know if anyone else gets this but did you mark the file for sharing as Dropbox gives me a

There was an error downloading your file

message?

 

[mumps]

Try:

Sub DeleteRows()
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:="=M*"
    Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("C1").AutoFilter
End Sub

 

[Fluff]

Just realised it should be OR not AND

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

 

[ellen01]

Genius!! That solved it. It requires such perfect logic, which is what I love about coding! Thank you!

Just realised it should be OR not AND

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

 

[Fluff]

Glad it's resolved & thanks for the feedback