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

Report
-
 Riteon -
Hello,

I'm aware of the post here

https://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

1 reply

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

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!