Excel VBA separate line break data into different rows for multiple columns

Hi all I tried to realise the outcome as following, I can write only for one column, could you please kindly to modify the code? ---------------------------------------------- Public Sub separ...

developer

excel

Feb 27, 2023

Try this macro:

Sub SplitLines()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim n As Long
    Dim a() As String
    Dim u As Long
    Dim i As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    n = Cells(1, Columns.Count).End(xlToLeft).Column
    For r = m To 2 Step -1
        a = Split(Cells(r, 2).Value, vbLf)
        u = UBound(a)
        If u > 0 Then
            For i = 1 To u
                Cells(r + 1, 1).EntireRow.Insert
                Cells(r + 1, 1).Value = Cells(r, 1).Value
            Next i
            For c = 2 To n
                a = Split(Cells(r, c).Value, vbLf)
                For i = 0 To u
                    Cells(r + i, c).Value = a(i)
                Next i
            Next c
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Sub SplitLines() Dim r As Long Dim m As Long Dim c As Long Dim n As Long Dim a() As String Dim u As Long Dim i As Long Application.ScreenUpdating = False m = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(1, Columns.Count).End(xlToLeft).Column For r = m To 2 Step -1 a = Split(Cells(r, 2).Value, vbLf) u = UBound(a) If u > 0 Then For i = 1 To u Cells(r + 1, 1).EntireRow.Insert Cells(r + 1, 1).Value = Cells(r, 1).Value Next i For c = 2 To n a = Split(Cells(r, c).Value, vbLf) For i = 0 To u Cells(r + i, c).Value = a(i) Next i Next c End If Next r Application.ScreenUpdating = True End Sub

Forum Discussion

Excel VBA separate line break data into different rows for multiple columns

Resources