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
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