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

Question

Hi all&nbsp;I tried to realise the outcome as following, I can write only for one column, could you please kindly to modify the code?&nbsp;----------------------------------------------Public Sub separate_line_break()target_col = "B"ColLastRow = Range(target_col &amp; Rows.Count).End(xlUp).RowIf InStr(Rng.Value, vbLf) ThenRng.EntireRow.CopyRng.EntireRow.InsertRng.Offset(-1, 0) = Mid(Rng.Value, 1, InStr(Rng.Value, vbLf) - 1)Rng.Value = Mid(Rng.Value, Len(Rng.Offset(-1, 0).Value) + 2, Len(Rng.Value))End IfNextColLastRow2 = Range(target_col &amp; Rows.Count).End(xlUp).RowFor Each Rng2 In Range(target_col &amp; "1" &amp; ":" &amp; target_col &amp; ColLastRow2)If Len(Rng2) = 0 ThenRng2.EntireRow.DeleteEnd IfNextApplication.ScreenUpdating = TrueEnd Sub----------------------------------------------Original Excel:&nbsp;The outcome I hope:&nbsp;&nbsp;

hansvogelaar · Accepted Answer

Tiffany_Jo&nbsp;
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 &gt; 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

1 Reply

Resources