Using a Macro to Split a Cell into Multiple Rows in Excel

This is an overview:

Excel Macro to Split a Cell into Multiple Rows

Step 1 – Open the VBA Editor in Excel

Select the cell containing the data you want to split, here B2 .
Press ALT+F11 to open the VBA window.
Select Insert >> Module.

Excel Macro to Split a Cell into Multiple Rows

Step 2 – Create a Sub Procedure

Sub SplitCell()

End Sub

Step 3 – Declare the Necessary Variables

Sub SplitCell()
Dim Cell, SplitCell() As String
End Sub

The Cell variable takes the value of the active cell. The SplitCell() variable stores the array of rows in which data will be split.

Step 3 – Copy the Data to a New Cell

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
End Sub

ActiveCell.Copy copies the data from the currently active cell.

ActiveCell.Offset(2, 0) is the destination cell: 2 rows below the active cell.

Step 4 – Select the New Cell

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
End Sub

ActiveCell.Offset(2, 0).Select activates the new cell (data in the original cell remains unchanged).

Step 5 – Set the Value of the Variables

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
Cell = ActiveCell.Value
SplitCell = Split(Cell, ",")
End Sub

Cell = ActiveCell.Value specifies that the Cell variable takes the value of the new active cell.

SplitCell = Split(Cell, “,”) splits the value stored in the Cell variable considering the comma (,) as the split criteria. You can use other delimiters (. ; / ) inside the double quotes (“ ”).

Step 6 – Using a For Loop to Split Data

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
Cell = ActiveCell.Value
SplitCell = Split(Cell, ",")
For i = 0 To UBound(SplitCell)
Next i
End Sub

The For Loop splits the data whenever a comma is found.

UBound(SplitCell) refers to the largest possible size of the SplitCell array.

Step 7 – Filling Rows with the Split Data

Sub SplitCell()
Dim Cell, SplitCell() As String
ActiveCell.Copy ActiveCell.Offset(2, 0)
ActiveCell.Offset(2, 0).Select
Cell = ActiveCell.Value
SplitCell = Split(Cell, ",")
For i = 0 To UBound(SplitCell)
ActiveCell.Offset(i, 0).Value = SplitCell(i)
Next i
End Sub

ActiveCell.Offset(i, 0).Value = SplitCell(i) returns the value of each row in the SplitCell array.