Delete a row of data then move on to the next unprocessed row

Question

I adapted code I found.

It copies an entire row to another worksheet if there is data in column O. Then it deletes the row from the active sheet.

It falls over at Next Cell. When it finds data it needs to delete, it deletes it, then the Next Cell command moves it on to the next row, but it effectively jumps a row.

If it finds data in row 15, it copies it, deletes row 15, but on the next loop I need the code to look at row 15 again as it is now new data in that row - but it jumps on to row 16 thus not reviewing the data which is now on row 15. So if there are two rows together than need action, the second one gets missed out.

When a row is deleted I need the code to hold back from moving to the next row as the deletion has already effectively moved me on to the next row.

Sub Test()

Dim Cell As Range

With Sheets(1)
    ' loop column O until last cell with value (not entire column)
    For Each Cell In .Range("O2:O" & .Cells(.Rows.Count, "O").End(xlUp).Row)
        If Cell.Value <> "" Then
            .Rows(Cell.Row).Copy Worksheets("Customer Support").Range("A" & Rows.Count).End(3)(2)      
            Rows(Cell.Row).EntireRow.Delete
        End If
    Next Cell
End With

End Sub

Answer 1

One method is to store the rows that you want to delete in a variable, but don't actually delete them until you finish looping through them all. That way they won't move around and you won't skip any.

Sub Test()

Dim Cell As Range
Dim rngDelete As Range

With Sheets(1)
    ' loop column O until last cell with value (not entire column)
    For Each Cell In .Range("O2:O" & .Cells(.Rows.Count, "O").End(xlUp).Row)
        If Cell.Value <> "" Then
          .Rows(Cell.Row).Copy Worksheets("Customer Support").Range("A" & Rows.Count).End(3)(2)
          
        'add row to be deleted
        If rngDelete Is Nothing Then Set rngDelete = Cell
        Set rngDelete = Union(rngDelete, Cell)
            
        End If
   
    Next Cell
    
    'Delete unwanted rows
    If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
        
End With


End Sub

Another method is to use a For i = LastRow to 1 Step -1 counter, which starts with the last row and works backwards. That way when you delete a row, you don't have to worry about skipping the row beneath it.