4
Thanks

A few words of thanks would be greatly appreciated.

How to Repeat Rows a Specified Number of Times on Excel

Rows in an Excel worksheet can be repeated a specified number of times with the help of a user-defined function. A user defined function can be written to repeat rows a specified number of times. The Selection.insert command can be used to insert rows in the worksheet of Excel Office software and there is no need to write the code from scratch as code snippets are freely available for use in worksheets. However, one must ensure that the user-defined function or subroutine to repeat rows a specified number of times is inserted in the right place.




How to Repeat Rows a Specified Number of Times

The situation in question here is how to repeat rows in a spreadsheet by a certain number of times specified in the cell present in that row i.e. from this table:

Column A Column B    
Peter 3
James 7
David 4


We want to produce this table:
Column A Column B    
Peter 3
Peter 3
Peter 3
James 7
James 7
James 7
James 7
James 7
James 7
James 7
David 4
David 4
David 4
David 4

Code to Repeat Rows a Specified Number of Times

Users can try this code:

Sub CopyData()   
Dim lRow As Long
Dim RepeatFactor As Variant

lRow = 1
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "B")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select
Selection.Insert Shift:=xlDown

lRow = lRow + RepeatFactor - 1
End If

lRow = lRow + 1
Loop
End Sub


Image: ©123RF.com
4
Thanks

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.

Related

This document, titled « How to Repeat Rows a Specified Number of Times on Excel », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments