Creating a new row from one line where a single cell has multiple values.

[Bluepig70]

Good morning all,

First time poster so go easy !

I have a sheet which contains data held in 3 columns. One of the columns holds data with line feeds separating the columns. I would like to know how I could turn something like this :-

CELL A | CELL B | CELL C
==================================================
Class A | History | Fred <Ctrl+J> Wilma <Ctrl+J> Barney <Ctrl+J>

Into

CELL A | CELL B | CELL C
=====================
Class A | History | Fred
Class A | History | Wilma
Calss A | History | Barney

I've seen multiple scripts before however none allow for the other values to be replicated to the new line ?

Any help would be greatfully received !

 

[Bluepig70]

Cell C values are separated by line feeds, by way of clarification.

 

[wideboydixon]

Right click the sheet name, select "View Code" to bring up the VBA editor. Now right click the VBAProject for your sheet and select Insert->Module. Paste the following in to the editor:

Option Base 0
Public Sub SplitCells()

Dim lastRow As Long
Dim rowLoop As Long
Dim aValue As String
Dim bValue As String
Dim splitValues() As String
Dim splitLoop As Long

lastRow = Range("A" & Rows.Count).End(xlUp).Row

rowLoop = 0
Do While rowLoop < lastRow
    rowLoop = rowLoop + 1
    aValue = Cells(rowLoop, 1).Value
    bValue = Cells(rowLoop, 2).Value
    splitValues = Split(Cells(rowLoop, 3).Value, vbLf)
    If UBound(splitValues) > 0 Then
        Rows(CStr(rowLoop + 1) & ":" & CStr(rowLoop + UBound(splitValues))).Insert
        For splitLoop = 0 To UBound(splitValues)
            Cells(rowLoop + splitLoop, 1).Value = aValue
            Cells(rowLoop + splitLoop, 2).Value = bValue
            Cells(rowLoop + splitLoop, 3).Value = Replace(splitValues(splitLoop), vbCr, "")
        Next splitLoop
        rowLoop = rowLoop + UBound(splitValues)
        lastRow = lastRow + UBound(splitValues)
    End If
Loop

End Sub

Back to your Excel sheet and use Alt+F8 to bring up the list of macros. Select SplitCells and run it. Please save your sheet before running the code just in case.

WBD