VBA -剪切每行中的最后一项并粘贴到新列中。行的长度不同

Sub CutAndPasteLastItem()
    Dim lastColumn As Integer
    Dim lastRow As Integer
    Dim i As Integer
    
    lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lastRow
        ActiveSheet.Cells(i, lastColumn + 1).Value = Split(ActiveSheet.Cells(i, lastColumn).Value, " ")(UBound(Split(ActiveSheet.Cells(i, lastColumn).Value, " ")))
        ActiveSheet.Cells(i, lastColumn).Value = Left(ActiveSheet.Cells(i, lastColumn).Value, Len(ActiveSheet.Cells(i, lastColumn).Value) - Len(Split(ActiveSheet.Cells(i, lastColumn).Value, " ")(UBound(Split(ActiveSheet.Cells(i, lastColumn).Value, " ")))))
    Next i
End Sub