Thread: Delete current row and Excel updates row number making the loop crash. Help

Hi everyone. I'm writing this code to first sort data and clean up the data.

Here is a brief about what I want to achieve, first I will sort the data by Asset (ASC) and the TargetDate (ASC). Second, I will go over each block of Asset, I use the TargetDate to delete any "RD" row that fall above (or happen before) the PJ. Also, if an PJ is single, which means it does not have any RD happen after (for example, row 16 should be moved to other sheet) will be moved. (Note that PJ and RD associate with other by AssetNum, that is why I sort by AssetNum ASC first then TargetDate ASC).If any Asset block that contain only RD, then they will be deleted (the RD block above row 16 should will be deleted).

The code I wrote for macro myAnalyze so when users run it, it will do all the tasks. Actually, if you see the there is the DayAfter which will calculate the day of the RD to the closest PJ above it in a Asset block and I still figuring it out, but now I'm stuck with the Delete function. The problem I got is the loop in the DeleteFosterRDorMovePJ. For example, when I run the loop to delete some row within a range. When row is delete, Excel auto update the rows (E.g: range is from row 6 to 11 and when I delete row 8, the row 9 will then become row 8 and 10 becomes 9, you know what I mean), and my code won't work correctly no more.

I have one week VBA experience so if I wrote silly code, please tell me to fix. Everything is my research online so far.
There are silly data in the attachment. I only keep the key fields.

Public Sub myAnalyze()
Dim BlockFirstRow, BlockLastRow  As Integer
BlockFirstRow = 2


'
'First we will do a data sorting
Call mySort


'=============================================
Do Until BlockFirstRow > ActiveSheet.UsedRange.Rows.Count
        BlockLastRow = FindLastRow(BlockFirstRow)
    
    Call DeleteFosterRDorMovePJ(BlockFirstRow, BlockLastRow)
    BlockFirstRow = BlockLastRow + 1
    
Loop
End Sub

' This function return the first and last row of an Asset Group block
Function FindLastRow(ByVal firstRow As Integer) As Integer

        Dim StringCondition As String
        StringCondition = ActiveSheet.Cells(firstRow, 9).Value '9 is for Col I, AssetNum
        Dim i As Integer
        i = firstRow
        Dim lastRow As Integer
        
        Do Until ActiveSheet.Cells(i, 9).Value <> StringCondition
        lastRow = i
        i = i + 1
        Loop
FindLastRow = lastRow '
End Function

 Function DeleteFosterRDorMovePJ(ByVal nFirst As Integer, ByVal nLast As Integer)  ' this will delete the RD's having no P* above it, within an asset block or move the single PJ to another sheet
    Dim a, b, c As Integer
    a = nFirst
    b = nLast

        If a = b Then 'the block of AssetNum is only 1 row
            'check if the one-row block is a PJ
            'if it's a PJ, code to move it to another sheet, if not PJ, it is single RD with single Assetnumber making an asset block then we can delete it
            If Cells(a, b).Value = "RD" Then
                Rows(a).Delete
                Exit Function
            Else ' not RD, then it could be PJ, code to move it to another sheet, dont know how to move the row to another sheet yet!
            
            ' move code here
                Rows(a).Interior.ColorIndex = 3 ' filling red. For testing only
                Exit Function
            End If
        Else ' a is not equal b then the Asset block is more than 1 row
            'For a = nFirst To b = nLast

' I wanted to do For a to b but if the row a and b is next to each other (a=2, b=4), then it will not go to the code inside
             Do Until a > b
                If Cells(currentRow, 2).Value = "RD" Then
                    Rows(a).Delete
                Else ' if it's not an RD, then it could or should be PJ
                    'Exit For
                    Exit Do
                End If
                a = a + 1
            Loop
            'Next
        End If
    
   
End Function
Sub BoldPJRow()
Dim r As Integer
For r = 2 To ActiveSheet.UsedRange.Rows.Count
    If ActiveSheet.Cells(r, 2) = "PJ" Then
    ' code to format whole row BOLD
    Rows(r).Font.Bold = True
    End If
Next

End Sub
Sub mySort()
'
' mySort Macro
' Macro recorded 2/2/2012 by me
'Sort by AssetNum (ASC), ActualFinish (ASC)

'
    Range("A1:L1280").Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:= _
        Range("G2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
End Sub