How do i copy rows from one worksheet and then delete them after it has moved?

Question

hi,

How do i copy rows from one worksheet programatically using VBA and then delete them after it has moved? I don't seem to be deleting all records based on the criteria, i'm searching for.

Dim lRowCounter as Long, lTotalRows as Long,sCommPerUnit  as String,lExceptionRowCounter  as Long

lTotalRows = 10

For lRowCounter = 1 To lTotalRows

    'If Row has no mapping, move it to Exceptions Report
    sCommPerUnit = Trim(rRange.Offset(lRowCounter, 2))

    If (sCommPerUnit = "SOMETHING") Then

        lExceptionRowCounter = lExceptionRowCounter + 1
        'Move row to Exception Report Worksheet
        rExceptionRange.Offset(lExceptionRowCounter, 1) = Trim(rRange.Offset(lRowCounter, Output_Order))

        'Delete Row from Report
        rRange.Offset(lRowCounter, 1).EntireRow.Delete xlShiftUp
    End If
Next

regards

Kojo

Answer 1

You are deleting the first row then shifting up, but incrementing to the next row. Meaning, you are skipping row. I would always delete the first row and shift up. Then on the next loop, the next row will be the first row again.

    'If Row has no mapping, move it to Exceptions Report 
    sCommPerUnit = Trim(rRange.Offset(0, 2)) 

    ...

    'Move FIRST row to Exception Report Worksheet'S LAST ROW
    rExceptionRange.Offset(lExceptionRowCounter, 1) = Trim(rRange.Offset(0, Output_Order)) 

    'Delete FIRST Row from Report 
    rRange.Offset(0, 1).EntireRow.Delete xlShiftUp 

Answer 2

When deleting rows in a range, it's almost always better to start with the last row and work backwards. This means that deleting a row doesn't change anything about the rows you have yet to look at

Dim lRowCounter as Long, lTotalRows as Long,sCommPerUnit  as String,lExceptionRowCounter  as Long

lTotalRows = 10

For lRowCounter = lTotalRows To 1 Step -1

    'If Row has no mapping, move it to Exceptions Report
    sCommPerUnit = Trim(rRange.Offset(lRowCounter, 2))

    If (sCommPerUnit = "SOMETHING") Then

        lExceptionRowCounter = lExceptionRowCounter + 1
        'Move row to Exception Report Worksheet
        rExceptionRange.Offset(lExceptionRowCounter, 1) = Trim(rRange.Offset(lRowCounter, Output_Order))

        'Delete Row from Report
        rRange.Offset(lRowCounter, 1).EntireRow.Delete xlShiftUp
    End If
Next lRowCounter