Sub CellSplitter()
'https://excelribbon.tips.net/T009396_Splitting_Information_into_Rows.html
' James has some data in a worksheet that is contained in a series of rows.
' One of the columns in the data includes cells that have multiple lines per cell.
' (The data in the cell was separated into lines by pressing Alt+Enter between items.)
' James would like to split this data into multiple rows. For instance, if there were
' three lines of data in a single cell in the row, then the data in that cell should be
' split out into three rows.
' Note that in order to run the macro, you will need to specify, using the iColumn variable,
' the column that contains the cells to be split apart. As written here, the macro splits
' apart info in the fourth column. In addition, the split-apart versions of the cells are
' stored in a new worksheet, so that the original worksheet is not affected at all.
' For some reason ActiveSheet is picking the current active sheet and so when
' Worksheets.Add is executed, it changes to the activeworksheet instead of when the variable
' was created. The below modification fixes it.
Dim Temp As Variant
Dim CText As String
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim iColumn As Integer
Dim lNumCols As Long
Dim lNumRows As Long
iColumn = 1
Set wksSource = ActiveSheet
lNumCols = Cells(1, Columns.Count).End(xlToLeft).Column
lNumRows = Cells(Rows.Count, 1).End(xlUp).Row
Set wksNew = Worksheets.Add
iTargetRow = 0
With wksSource
For J = 1 To lNumRows
CText = .Cells(J, iColumn).Value
Temp = Split(CText, Chr(10))
For K = 0 To UBound(Temp)
iTargetRow = iTargetRow + 1
For L = 1 To lNumCols
If L <> iColumn Then
wksNew.Cells(iTargetRow, L) _
= .Cells(J, L)
Else
wksNew.Cells(iTargetRow, L) _
= Temp(K)
End If
Next L
Next K
Next J
End With
End Sub