1

My question is an extension to this, Split text in cells at line breaks, where, I need to split text in cells with line break into separate rows, NOT only for just one column, but for multiple columns.

A screenshot of my data: screenshot of my data

Community
  • 1
  • 1
Xinxin Li
  • 51
  • 1
  • 1
  • 9

1 Answers1

2

Just building on what was done in your linked answer:

Sub JustDoIt2()
    'working for active sheet
    'copy to the end of sheets collection
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    Dim tmpArr As Variant
    Dim Cell As Range
    For Each Cell In Range("A2", Range("A2").End(xltoright).End(xlDown)) 
        If InStr(1, Cell, Chr(10)) <> 0 Then
            tmpArr = Split(Cell, Chr(10))
            If Cell.Offset(1) <> Cell Then
                Cell.EntireRow.Copy
                Cell.Offset(1, 0).Resize(UBound(tmpArr), 1). _
                    EntireRow.Insert xlShiftDown
            End If
            Cell.Resize(UBound(tmpArr) + 1, 1) = Application.Transpose(tmpArr)
        End If
    Next
    Application.CutCopyMode = False
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I tried the solution by @ScottCraner on my set of data, and it doesn't seem to work if some of the columns contain the same data. E.g., if I had two rows where column H contains 1, 2, and 3, all with line feeds, the second row actually gets overwritten. I can't seem to figure out the fix, but I'm working on it. It does seem to work well on data where the cell data is different cell to cell. – M J Jan 24 '23 at 21:38