A few words of thanks would be greatly appreciated.

Excel - Split data into several rows


I would like to split a row of data into several rows of same data in MS Excel. For example, I have an item with standard packing size of 100pcs per box. My customer places an order for 1,000pcs. I would like to have this order item repeated 10 times in the worksheet with each row having 100pcs as the box quantity.


First select the entire row you would like to repeat.

Run the following code:

Sub RepeatRow()  
Dim iRow As Integer  
Dim x As Integer  
Dim y As Integer  

iRow = ActiveCell.Row  
x = InputBox("Number of times you want to display the selected row:")  
y = 1  

Selection.Insert Shift:=xlDown  
y = y + 1  
Loop Until y = x  

Application.CutCopyMode = False  

End Sub
  • Running the code will let you input the number of times you want to see the row repeated.
  • In your case select the row with 100pcs in it, run the code, input 10 and you will see that your one row turned into 10 rows.

Thanks to Trowa for this tip.


A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Split data into several rows », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!