Updating previous row data with unique ID using new data dropped at the same sheet in Excel

Question

This is more for optimization purposes, as the code currently works based on my workflow, but it can take a while when there are thousands of rows to process.

Essentially, my current workflow involves appending monthly data to a sheet with client name included. Each client is tied with a unique ID. Of course, client names get changed over time, and it messes up the pivot table linked to this sheet when that happens.

My solution is to sort the sheet based on the Unique ID of the client, then the date of the row, from newest to oldest. Then, run a macro that will copy the topmost data downwards, since the topmost data would be the newest name for the client.

Are there any further optimizations I could do for this code to make it run faster for bigger datasets? It starts to chug when it's processing over 100K rows.

'A code to disable screenupdates, calculations, etc already runs before this code.
'ColID = Column that contains the Unique ID. Ex: Column S.
'ColRangeStart & ColRangeEnd select which columns from the top are being copied. Ex: Column T:V
For i = 3 To lrow - 1 Step 1
    With ActiveSheet
        If .Range(ColID & i).Value <> "" Then
            IDToFind = .Range(ColID & i).Value
            lastFoundRow = .Columns(ColID).Find(What:=IDToFind, Lookat:=xlWhole, SearchDirection:=xlPrevious).Row
            
            If .Range(ColID & i).Row = lastFoundRow Then
                GoTo SkipLoop
            Else
                If WorksheetFunction.CountA(.Range(ColRangeStart & i & ":" & ColRangeEnd & i)) <> 0 Then
                    .Range(ColRangeStart & i & ":" & ColRangeEnd & i).Copy .Range(ColRangeStart & i & ":" & ColRangeEnd & lastFoundRow)
                    i = lastFoundRow
                End If
            End If
        Else
            .Range(ColID & i & ":" & ColRangeEnd & i).Copy .Range(ColID & i + 1 & ":" & ColRangeEnd & i + 1)
            GoTo SkipLoop
        End If
    End With
SkipLoop:
    DoEvents
    ProgressBar.lblCount.Caption = "Processing " & i & " out of " & lrow - 1 'Got a GUI Progress bar for this code
    ShowProgress (i)
Next

Answer 1

Something like this would be much faster: pulling the data into arrays is quicker than iterating over cells on the worksheet row-by-row.

Sub Tester()

    Dim rngId As Range, arrId, rngInfo As Range, arrInfo, currId, idRow As Long, id
    Dim r As Long, c As Long, ubInfoCols As Long, lrow As Long, ws As Worksheet
    
    Set ws = ActiveSheet
    lrow = ws.Cells(Rows.count, "A").End(xlUp).Row
    
    Set rngId = ws.Range("S3:S" & lrow)
    arrId = rngId.Value  'read all id's
    
    Set rngInfo = ws.Range("T3:V" & lrow)
    arrInfo = rngInfo.Value 'read all info
    ubInfoCols = UBound(arrInfo, 2) '# of cols
    
    currId = Chr(0) ' any unlikely value
    For r = 1 To UBound(arrId, 1)
        id = arrId(r, 1)
        If Len(id) > 0 Then      'any id present?
            If id <> currId Then 'new Id?
                currId = id 'set to current id
                idRow = r   'remember start row
            Else
                'same id: copy info down
                For c = 1 To ubInfoCols
                    arrInfo(r, c) = arrInfo(idRow, c)
                Next c
            End If
        End If
    Next r
    
    rngInfo.Value = arrInfo 'update info column data
            
End Sub