Compile Error Next without For

[Will85]

Hello,

Another board member provided me with this VBA but has since vanished, leaving me with an error. I am teaching myself about loops (pretty cool!), but still cant figure out what is missing. I get a compile error, with the first Next highlighted. But I think its the start of the code that's wrong, not the end? But I am still just learning about loops.

Sub DeleteRowsandComboboxes()
    Dim Cel As Range
    Dim Shp As Shape
    Dim ro As Integer
    Set Cel = Cells(1, 1)
    
    Do Until Cel.End(xlDown).Row = 1048576
    Set Cel = Cel.End(xlDown)
        ro = Cel.Row
        If Cel = "Yes" Then
            Rows(ro).Delete
            Set Cel = Cells(ro, 1)
        Else
            Set Cel = Cel.End(xlDown)
        End If
    Next
    
    For Each Shp In ActiveSheet.Shapes
        If Shp.Type = 8 Then
            Shp.Select
            If Selection.LinkedCell = "#REF!" Then Shp.Delete
        End If
    Next


End Sub

 

[mole999]

did you have a working version, as for why you are testing a million rows, i'm sure it can be pulled back inline. how many rows usually involved

 

[Will85]

I dont have a working version. i noticed that too, I think he just assumed to test all rows. in reality I can make an assumption that it would never surpass 500 rows (probably much lower than that if it needs to be, but 500 just to be safe)

 

[mole999]

many formulas, they can recalculate every time you change a line, so would normally work up, rather than down

 

[Will85]

Not sure I follow. This macro would only be run at the users discretion and probably rarely.

 

[mole999]

some formulas are volatile, so recalculate every sheet change, and deleting a line would be such a trigger, if you had 500 rows of volatile formulas that does take time

 

[Will85]

Ok. Well Im still no closer to understanding the compile error . . . .

 

[mole999]

have a look here > https://stackoverflow.com/questions/15375054/vba-macro-to-delete-rows-quickly about half way down

 

[mole999]

I'm not certain but you have Do Loop and For Next, and we seem to have Do Next

 

[Will85]

Ok. Well if your not certain, and Im not certain, I guess were not getting anywhere.