Split a Cell into Multiple Rows (VBA Function)

[Alexio2801]

Hi everyone,
regarding the procedure you posted in that page:

Using a Macro to Split a Cell into Multiple Rows in Excel - 8 Steps - ExcelDemy

This article illustrates how to create an excel macro to split a cell into multiple rows. It also highlights 2 more simple methods to do that.

www.exceldemy.com

...Should it be possible to create new blank rows automatically, independently of the number of values in the cell?
I'm using your macro but in my case I've not blank rows under the cell where I'm applying the funtion, so I've to create the blank rows before I run the macro (it overwrites anything below if you don 't prepare blank records).

Kind regards,
Alessio.

 

[Lutfor Rahman Shimanto]

Hi everyone,
regarding the procedure you posted in that page:

Using a Macro to Split a Cell into Multiple Rows in Excel - 8 Steps - ExcelDemy

This article illustrates how to create an excel macro to split a cell into multiple rows. It also highlights 2 more simple methods to do that.

www.exceldemy.com

...Should it be possible to create new blank rows automatically, independently of the number of values in the cell?
I'm using your macro but in my case I've not blank rows under the cell where I'm applying the funtion, so I've to create the blank rows before I run the macro (it overwrites anything below if you don 't prepare blank records).

Kind regards,
Alessio.

Hello Alexio2801

Thank you for reaching out with your comment. You encountered an issue, such as not having blank rows under the cell where you are applying the procedure, so you had to create a blank row before you ran the macro. To be more specific, it overwrites anything below if you don't prepare empty records. To fix this, you must modify the code. It would help if you used" ' " as a delimiter; the rest of the code should be the same.

You can use the Excel VBA code I modified to solve your issue. Likewise, select the intended cell, run the code and good luck.

Sub SplitCellExcelDemy()
    
    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

Regards
Lutfor Rahman Shimanto

 

[Alexio2801]

Hi and thanks for your reply.
As you can see on my .xlsm file I already changed the code with " ' " and it overwrites anything below, but I need to create automatically new empty rows below the cell where I apply the macro.
I need a way to do that considering the different number of values that could be present in the cell. If a cell contains 20 values I need new 20 empty rows below, if a cell contains 7 values I need new 7 empty rows below, etc... I'm working with many thousands of rows and creating all empty rows manually is becoming very very difficult.

 

[Lutfor Rahman Shimanto]

Hi and thanks for your reply.
As you can see on my .xlsm file I already changed the code with " ' " and it overwrites anything below, but I need to create automatically new empty rows below the cell where I apply the macro.
I need a way to do that considering the different number of values that could be present in the cell. If a cell contains 20 values I need new 20 empty rows below, if a cell contains 7 values I need new 7 empty rows below, etc... I'm working with many thousands of rows and creating all empty rows manually is becoming very very difficult.

Dear Alexio2801,

I want to thank you for explaining your problem with such clarity. I have successfully solved the issue you were facing with the help of an Excel VBA code. I'm attaching the Workbook we used to look into your problem. Per your requirement, I have created a solution that will automatically create new empty rows based on the number of values in a cell. With this solution, you can handle thousands of rows manually without creating blank rows, thus saving you much time and effort.

To make it even more convenient for you, I created a button named SPLITE and assigned the VBA macro to it. Therefore, you can click the button, and the VBA code will run automatically, creating new empty rows as required.

INPUT:

​

OUTPUT:

​

Sub SplitCellWithoutOverWriting()

    Dim Cell, SplitCell() As String
  
    ActiveCell.Copy ActiveCell.Offset(0, 0)
    ActiveCell.Offset(0, 0).Select
    Cell = ActiveCell.Value
    SplitCell = Split(Cell, "'")
  
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + UBound(SplitCell) + 1).Insert Shift:=xlDown
  
    For i = 0 To UBound(SplitCell)
        ActiveCell.Offset(i + 1, 0).Value = SplitCell(i)
    Next i

End Sub

Thank you for using our platform, and I look forward to hearing from you soon. If you need further assistance or have other questions, please don't hesitate to contact us.

Best regards,
Lutfor Rahman Shimanto