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 replace the first NEXT and use a LOOP ?

 

[Will85]

yes, and it still didnt work, but I have a solution for the find and delete rows.

Now I am struggling to get the delete combobox code to work, nothing happens when I run it and the combobox is still on the sheet.

 

[mole999]

is the combo in a cell, then .range("cell").clear

 

[Will85]

This appears to be the solution I need, working so far.

Sub DeleteRowsandComboboxes()
ActiveSheet.Unprotect
On Error Resume Next
With Range("C5:C" & Cells(Rows.Count, "C").End(xlUp).Row)
.Replace "YES", "#N/A", xlWhole, , False, , False, False
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
Dim OleObj As OLEObject
For Each OleObj In ActiveSheet.OLEObjects
With OleObj
If .progID = "Forms.ComboBox.1" And .LinkedCell = "#REF!" Then OleObj.Delete
End With
Next OleObj
ActiveSheet.Protect
End Sub