VBA delete row working in one for loop but not another

Question

My goal is to have my program find all rows from a given table on sheet1 that either contain “Cancelled” in column AB or are not null in column Z and move them to sheet2, then delete them from sheet1. The first for loop (handling the “Cancelled” rows) works as intended. The second for loop however is throwing a run time error and saying “Delete method of range class failed”.

I tried removing only the myCell.EntireRow.Delete line from the loop and it worked fine (minus deleting the rows at the end). Why would this code succeed in the first loop but not in the second?

Sub move_rows_to_another_sheet_cust()
For Each myCell In Worksheets("Sheet1").Columns(28).Cells
If myCell.Value = "Cancelled" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
For Each myCell In Worksheets("Sheet1").Columns(26).Cells
If myCell.Value <> "" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
End Sub

Why do you need the second loop? You can have multiple conditions in your single if-statement. — Cyril
– Cyril, Commented Sep 26, 2024 at 18:48
It is always dangerous to delete things from a collection that you are looping over. When you delete row 29, for example, what used to be row 30 is now row 29. If it were me, I'd restart the loop every time I deleted a row. — Tim Roberts
– Tim Roberts, Commented Sep 26, 2024 at 18:48
I used multiple loops because they are each scanning different columns. Is there a better approach? @Cyril — Kat Brown
– Kat Brown, Commented Sep 26, 2024 at 18:56

Cyril · Accepted Answer · 2024-09-26 19:05:49Z

0

A single loop could work such that:

Sub move_rows_to_another_sheet_cust()
    With Sheets(1)
        Dim lastRow as Long:  lastRow = .Cells(.Rows.Count,1).End(xlUp).Row
        Dim rowNum as Long:  For rowNum = lastRow to 1 Step -1
            If .Cells(rowNum,28).Value = "Cancelled" Or .Cells(rowNum,26).Value <> "" Then
                .Rows(rowNum).Copy Sheets(2).Rows(Sheets(2).Cells(Sheets(2).Rows.Count,1).End(xlUp).Row+1)
                .Rows(rowNum).Delete
            End If
        Next rowNum
    End With
End Sub

edited Sep 26, 2024 at 19:05

answered Sep 26, 2024 at 18:54

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Cyril Over a year ago

Accidentially left in i instead of rowNum in original post, so edited. Assessing the individual cells in the column would allow you to avoid having a second loop.

Kat Brown Over a year ago

Tried updated script, getting error "Row method of delete class failed" attributed to .Rows(rowNum).Delete

Cyril Over a year ago

@KatBrown please make sure you grabbed the updated code. I assume your error is due to the previous code listing i instead of rowNum, where i = 0 and that cannot be a row.

Kat Brown Over a year ago

I am using the updated code, but noticing something odd. Despite throwing the error, if I let the code keep running in the background rather than ending or debugging, it actually works to do exactly what I want it to do, including the deletion. Have attempted multiple times and consistently works but immediately throws error. Any idea why this might be?

Kat Brown Over a year ago

Also I mistyped the error, it's "Delete method of range class failed"

|

Collectives™ on Stack Overflow

VBA delete row working in one for loop but not another

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related