Repeat rows in Excel: based on cell value, VBA

Repeat rows in Excel: based on cell value, VBA

Rows in an Excel worksheet can be repeated a specified number of times with user-defined functions. The Selection.insert command can be used to insert rows in an Excel sheet 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 

What is the VBA 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

Need more help with Excel? Check out our forum!

Excel