Excel - Repeat rows a specified number of timesSolved/Closed

Question

Hello, 

I'm aware of the post here

https://ccm.net/apps-sites/software/1603-how-to-repeat-rows-a-specified-number-of-times-on-excel/

But I would like to just type a numerical number in the VBA formula itself. e.g. 10 to repeat each one 10 times.

Is there a simpler way to modify the VBA code so that it will do this without basing the multiplier on the value in column B

Thanks

Riteon

Riteon · Accepted Answer

Solved 

http://www.mrexcel.com/forum/excel-questions/860196-copy-values-column-insert-8-times.html

Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant
Dim Num As Integer
Num = InputBox("How many Times")
    lRow = 1
    Do While (Cells(lRow, "A") <> "")
           
        RepeatFactor = Num '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
    Application.CutCopyMode = False
    Range("A1").Select
    
End Sub

Excel - Repeat rows a specified number of times

1 response

Similar discussions

Subscribe To Our Newsletter!