Error 424: Looping using For - Next

[Steve2020]

Hi,

New to VBA here, getting error 424 object not found with this code after running, it runs only once perfectly but stops after 1 iteration. Consider "yyy" as all different variables (code I am using is below).

Any help would be appreciated

[TABLE="class: outer_border, width: 800"]
<tbody>[TR]
[TD]Sub DeleteSpecifcColumn()
Set MR = Range("A2:BT2")
For Each cell In MR
If cell.Value = "yyy" Then cell.EntireColumn.Delete
If cell.Value = "yyy" Then cell.EntireColumn.Delete
If cell.Value = "yyy" Then cell.EntireColumn.Delete
If cell.Value = "yyy" Then cell.EntireColumn.Delete
Next
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]



Thanks in advance

 

[Joe4]

When deleting row or columns, it is always best to loop through your range backwards. Otherwise, deleting the rows/columns shifts the rest of your range over to the left/up, and values will get missed.
Unfortunately, you cannot look through a range backwards. So you need to do it like this:

Sub DeleteSpecifcColumn()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Loop through columns A:BT backwards
    For c = 72 To 1 Step -1
        If (Cells(2, c) = "www") Or (Cells(2, c) = "xxx") _
            Or (Cells(2, c) = "yyy") Or (Cells(2, c) = "zzz") Then
            Columns(c).Delete
        End If
    Next c
        
    Application.ScreenUpdating = True
        
End Sub