Loop to cut and paste lines is skipping some lines

[TheRedCardinal]

Hi all,

I am writing a macro which does the following:

1. Looks through a set range by name of the header to the bottom row of the table
2. For each cell in that range if the value is nil, it cuts that row
3. It then pastes that row somewhere else in the sheet - in the next blank row at the bottom

My code looks like this:

Sub MoveDataNoInvoiceInPeriod()

'Once value is updated, anything with 0.00 can be removed
'Copied to bottom of sheet


    Set ws1 = Sheets("2. Final Data")
    ws1.Activate
    
    Dim ActiveRow As Long, PasteRow As Long, Value As Range
    
    PasteRow = Cells(Rows.Count, 1).End(xlUp).Row + 2
    lRow = Range("A1").End(xlDown).Row
    
    Cells(PasteRow, 1).Value = "Lines Removed From Intrastat - Invoice in different months"
    Cells(PasteRow, 1).Font.Bold = True
    PasteRow = PasteRow + 1
    
    With ws1
    
        Set Value = .Range("A1:Z1").Find("Adjusted Value")
        
        For Each CellA In .Range(Cells(2, Value.Column), Cells(lRow, Value.Column))
        
            If CellA.Value = 0 Then
                .Rows(CellA.Row).EntireRow.Cut
                .Rows(PasteRow).Insert
                
            End If
        Next CellA
    
    End With
    


End Sub

For explanation, PasteRow is not the same as LRow because some lines from the table have already been moved and so the "last row" of the sheet is not the last row of my table.

It kind of works, but when reconciling my data I found 3 lines with 0 value that hadn't been removed. When I returned to source data I saw that these lines were immediately under another line with 0 value. So obviously when it finds an entry in row 30 which is 0, it cuts it, but then the line below becomes row 30, and it seems to be skipping it.

I thought using the "For Each Cell In Range" format would fix this, compared to using a loop that used a For I = 1 to LRow / Next I counter system, but obviously I was wrong!

Is there an easy fix for this in VBA?

I suspect I'll have to implement it for all my loops now.

 

[TheRedCardinal]

I have "solved" this by just using a Counter loop with Step -1 which now works, but if anybody has a better solution then please let me know.

Otherwise this can be closed / deleted.

Thanks!