VBA: Start a new number sequence based on an adjacent cell change

lnmlilo The following procedure loads the data from column D into an array, then loops through each item and assigns the applicable line number to a new array. The results are then output to column E all at once, which is significantly faster than iterating through each cell in a range directly on the worksheet.

Sub AssignLineNumber()

'Load the data from column D into an array
    Dim ws As Worksheet, rowCount As Long, data As Variant
    Set ws = Application.ActiveSheet
    rowCount = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row - 2
    data = ws.Range("D3").Resize(rowCount).Value

'Generate line numbers in a new array
    Dim arr() As Variant, i As Long
    ReDim arr(1 To rowCount, 1 To 1)
    arr(1, 1) = 1
    For i = 2 To rowCount
        If data(i, 1) = data(i - 1, 1) Then
            arr(i, 1) = arr(i - 1, 1) + 1
        Else
            arr(i, 1) = 1
        End If
    Next i

'Output the results to column E
    ws.Range("E3").Resize(rowCount).Value = arr

End Sub

For more information on VBA arrays, please see: https://excelmacromastery.com/excel-vba-array/