使用反向循环。For i = lRow to 1 Step -1。也可以使用SPLIT()来分隔单词。
这就是你想要的吗？

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long, j As Long
    Dim Ar As Variant
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet2
    
    With ws
        '~~> Find last row in Column A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Reverse Loop in Column A
        For i = lRow To 1 Step -1
            '~~> Check if cell has vbLf
            If InStr(1, .Cells(i, 1).Value, vbLf) Then
                '~~> Split cell contents
                Ar = Split(.Cells(i, 1).Value, vbLf)
                
                '~~> Loop through the array from 2nd position
                For j = LBound(Ar) + 1 To UBound(Ar)
                    .Rows(i + 1).Insert
                    .Cells(i + 1, 1).Value = Ar(j)
                Next j
                
                '~~> Replace cells contents with content from array from 1st position
                .Cells(i, 1).Value = Ar(LBound(Ar))
            End If
        Next i
    End With
End Sub

之前

之后

这是我的解决方案，也适用于二维范围，它适用于选择，所以选择要拆分的单元格范围，然后运行代码。

Sub splitByNewLine()
    Dim pasteCell As Range, rowCumulationTotal As Integer
    rowCumulationTotal = 0
    Dim arr() As Variant
    arr = Selection
    Selection.Clear
    
    For i = 1 To UBound(arr)
        Dim rowCumulationCurrent As Integer, maxElemsOnRow As Integer
        rowCumulationCurrent = 0
        maxElemsOnRow = 0
        For j = 1 To UBound(arr, 2)
            Dim elems() As String, elemCount As Integer
            elems = Split(arr(i, j), vbLf)
            elemCount = UBound(elems)
            For k = 0 To elemCount
                Cells(Selection.Row + i + rowCumulationTotal + k - 1, Selection.Column + j - 1) = elems(k)
                If maxElemsOnRow < k Then
                    rowCumulationCurrent = rowCumulationCurrent + 1
                    maxElemsOnRow = k
                End If
            Next k
        Next j
        rowCumulationTotal = rowCumulationTotal + rowCumulationCurrent
    Next i
    Exit Sub
End Sub

输入：

输出：