Divide rows into multiple rows if a cell has more than 1 "row"

Re: Divide rows into multiple rows if a cell has more than 1 "row"

Can't download the sheet, at work waiting for some tests to run.

Something like this should help you though:

'Takes the input string and splits it into an array using a Line Feed as the delimiter
Public Function SplitLineBreaks(ByRef strInput as String) As Variant
    SplitLineBreaks = Split(strInput, vbLf)
End Function

So you need to feed in the value from the cell as the strInput and it will return an array with a minimum size of 0 to 0, or 0 to X based on the number of line breaks found in the string, with each element representing the string from the start/line break to the next line break/end.

Re: Divide rows into multiple rows if a cell has more than 1 "row"

Following on from TheGlovner's suggestion, try this. It puts the results on a new sheet

Sub x()


Dim r As Long, v, i As Long


With Sheet1
    For r = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
        v = Split(.Cells(r, "J"), vbLf)
        .Cells(r, 1).Resize(, 9).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(v) + 1)
        Sheet2.Cells(Rows.Count, "J").End(xlUp)(2).Resize(UBound(v) + 1) = Application.Transpose(v)
        Sheet2.Cells(Rows.Count, "K").End(xlUp)(2).Resize(UBound(v) + 1) = Application.Transpose(Split(.Cells(r, "K"), vbLf))
    Next r
End With


End Sub