Excel - Repeat rows a specified number of times [Solved/Closed]

Riteon - Jun 9, 2015 at 08:51 AM - Latest reply:  Riteon
- Jun 9, 2015 at 09:17 AM
Hello,

I'm aware of the post here

http://ccm.net/faq/7546-excel-repeat-rows-a-specified-number-of-times

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
See more 

1 reply

Best answer
3
Thank you
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

Thank you, Riteon 3

Something to say? Add comment

CCM has helped 1895 users this month