August 2016

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**. Some knowledge about programming concepts such as if-then-else conditions and do while loops might be necessary to write such a function. The **Selection.insert command** can be used to insert rows in the worksheet of Excel Office software. 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.

## Issue

**Does anyone know 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:

I need to produce this table:

*Any ideas on a simple way of doing this? *

## Solution

Try this:

## Note

Thanks to rizvisa1 for this tip on the forum.

See also :

i.e. from this table:

Column A Column B

Peter 3

James 7

David 4

I need 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

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

See also :

- Excel - Repeat rows a specified number of times
- Excel 2010 how to get 3 numbers copied down followed by the next number in the series repeated 3 times
- Excell repeat formulaa number of times and then add one
- Excel - Repeat rows a specified number of times [Solved] (Solved)
- How to repeat rows by x number of times (Solved)
- Excel - Repeat rows to a specific number of times (Solved)
- Formula to copy rows a set number of times (Solved)
- Copy rows a set number of times