Next without For VBA Error

[Will85]

Hello,

I am new to loops but learning. I cannot figure out why I am getting a compile error:

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

 

[mrshl9898]

The Next needs to be a loop.

For Each
****
Next

Do Until
****
Loop

[COLOR=#333333][COLOR=#333333]Sub DeleteRowsandComboboxes()[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Dim Cel As Range[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Dim Shp As Shape[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Dim ro As Integer[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Set Cel = Cells(1, 1)[/COLOR][/COLOR]

[COLOR=#333333][COLOR=#333333]Do Until Cel.End(xlDown).Row = 1048576[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Set Cel = Cel.End(xlDown)[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]ro = Cel.Row[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]If Cel = "Yes" Then[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Rows(ro).Delete[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Set Cel = Cells(ro, 1)[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Else[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Set Cel = Cel.End(xlDown)[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]End If[/COLOR][/COLOR]
[COLOR=#333333]Loop[/COLOR]

[COLOR=#333333][COLOR=#333333]For Each Shp In ActiveSheet.Shapes[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]If Shp.Type = 8 Then[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Shp.Select[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]If Selection.LinkedCell = "#REF!" Then Shp.Delete[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]End If[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Next[/COLOR][/COLOR]


[COLOR=#333333][COLOR=#333333]End Sub[/COLOR][/COLOR]

 

[Will85]

thank you. I am now getting an overflow error on

ro = Cel.Row

 

[Will85]

I changed my variable to long, and it appears to be running, but very slowly. been spinning its wheels for 5 minutes now.

I think I need to rethink my macro. I need a macro that will delete all rows in the spreadsheet if the value in column C from row 5 down = "Yes" There are breaks and blanks in between my "Yes" values, so it would have to go from the bottom up to determine the last row that could possibly need to be deleted.

 

[mrshl9898]

You'll probably find filtering and deleting to be a faster solution, looping through all rows can be very time consuming.

https://www.mrexcel.com/forum/excel...w-delete-autofiltered-rows-except-header.html