# How to Repeat Rows for a Specified Number of Times in Excel

Consider a dataset containing columns Item, their Grade, and Stock. We have to repeat some of its rows to make a bill.

### Method 1 – Using the Fill Feature to Repeat Rows a Specified Number of Times in Excel

#### Case 1.1 – Using the Fill Handle Tool

Steps

• Select the whole row that you need to repeat a specified number of times.
• Go to the bottom-right corner of the cell and the cursor will change to the Fill Handle icon (+).
• Click and drag the icon down to repeat rows.

• Once you have selected enough rows, stop dragging and release the mouse.

Read More: How to Repeat Rows in Excel at Bottom

#### Case 1.2 – Using the Fill Feature from Ribbon

Steps

• Select the row that is under the row you want to repeat. We want the cells B11:D11 to be repeated, so selected cells B12:D12.
• Go to the Home tab and, from the Editing group, click on Fill.
• Click on Down.

• You can see the repeated row.

• Repeat until you get enough copies.

Read More: Repeat Text in Excel Automatically

### Method 2 – Using VLOOKUP Function

In the following dataset, you can see the Item and Repeat Time columns. In the Repeat Time column, we put the number of times we want the rows to be repeated.

Steps

• Insert a new column B named Helper Column.
• Put 1 in cell B5.

• Use the following formula in cell B6.
`=B5+D5`

• Press Enter.
• Drag down the formula with the Fill Handle tool.

• Here’s the complete Helper Column.

• Make another column E and name it Column 2.
• Enter 1 to 15 in cells E5 to E20.

• Add a column F named Repeat.
• Use the following formula in cell F5.
`=VLOOKUP(E5,\$B\$4:\$C\$10,2)`

Formula Breakdown

• The VLOOKUP function looks up a certain value in an array based on specified criteria.
• VLOOKUP(E5,\$B\$4:\$C\$10,2) â†’ becomes
• Output: Laptop
• Hit Enter.
• Drag down the formula with the Fill Handle tool.

• You can see the complete Repeat column.

### Method 3 – Inserting VBA Code to Repeat Rows for a Specified Number of Times in Excel

Steps

• Go to the Developer tab and select Visual Basic.

• A VBA Editor window will appear.
• From the Insert tab, select Module.

• Insert the following code in the Module.
``````Sub Repeat_Data()
Dim x_range As Range
Dim in_range As Range, Outx_range As Range
xTitleId = "Repeat Rows a specified number of times"
Set in_range = Application.Selection
Set in_range = Application.InputBox(" select Range :", _
Set Outx_range = Application.InputBox("location (single cell):", _
xTitleId, Type:=8)
Set Outx_range = Outx_range.Range("A1")
For Each x_range In in_range.Rows
xValue = x_range.Range("A1").Value
xNum = x_range.Range("B1").Value
Outx_range.Resize(xNum, 1).Value = xValue
Set Outx_range = Outx_range.Offset(xNum, 0)
Next
End Sub``````

Code Breakdown

• We declared Repeat_Data as the Sub.
• We take x_range, in_range, and outx_range as Range.
• We used the FOR...NEXT loop to run the code until the last value is found.
• Go to the Developer tab and select Macros.

• Select the Sub Repeat_Data and click on Run.

• A prompt box appears where you have to input the range (\$B\$5:\$B\$10).
• Click OK to continue.

• Another prompt box appears. Select a cell where you want to show your output. We selected cell E5.
• Click OK to continue.

• We have got our specified number of repeated rows.

### Method 4 – Combining IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA Functions

Steps

• Put the items in the Item sheet.

• In the Repeat sheet, we want these items to be repeated in the empty cells of the following created table.

• Use the following formula in cell B11.
`=IF(ISBLANK(INDIRECT("Item!B"&ROW(B5))),INDIRECT("Repeat!B"&(ROWS(\$B\$5:B5)-(COUNTA(Item!B:B)-2))),Item!B5)`

Â Formula Breakdown

• The IF function makes a logical comparison between a value we want and the given value.
• The ISBLANK function returns TRUE or FALSE after checking on a cell reference.
• The INDIRECT function finds out the reference that is specified by the text string.
• The ROW function finds the row number of a cell reference.
• The ROWS function determines the number of rows of a range of cells.
• The COUNTA function counts the number of nonempty cells.
• IF(ISBLANK(INDIRECT(“Item!B”&ROW(B5))),INDIRECT(“Repeat!B”&(ROWS(\$B\$5:B5)-(COUNTA(Item!B:B)-2))),Item!B5) â†’ becomes
• Output: Laptop
• Press Enter.
• You can see the result in cell B11.
• Drag the formula to the right through the Fill Handle tool.

• Drag down the formula with the Fill Handle tool.

• You can see the repeated rows.

Read More: How to Make a Pattern Repeat in Excel

### Method 5 – Merging TRIM, TEXTJOIN, IFERROR, FILTERXML, REPT, SUBSTITUTE, and XLOOKUP Functions

In the following dataset, you can see the Item and Repeat Time columns.

Steps

• Use the following formula in cell E4.
`=TRIM(TEXTJOIN(" ", TRUE, REPT(ROW(B5:B100)&" ", C5:C100)))`

Formula Breakdown

• The ROW function finds the row number of a cell reference.
• The REPT function repeats the values a number of times.
• The TEXTJOIN function adds a number of text strings.
• The TRIM function trims the text string by removing extra spaces.
• TRIM(TEXTJOIN(” “,TRUE,REPT(ROW(B5:B100)&” “,C5:C100))) becomes
• Output: 5 5 6 6 7 7 7 8
• Press Enter.

• Add a Helper 2 column.
• Use the following formula in cell E7.
`=IFERROR(FILTERXML( "<a><b>"&SUBSTITUTE(F4," ","</b><b>")&"</b></a>", "//b"),"")`

Â Formula Breakdown

• The SUBSTITUTE function replaces the current text with another text string.
• The FILTERXML function returns certain XML data by using Xpath.
• The IFERROR function removes any error present in the formula.
• Press Enter.

• Add a Repeated Items column D.
• Use the following formula in cell D5.
`=XLOOKUP(\$F\$7# ,ROW(B5:\$B\$100 ),B5:B100)`

Formula Breakdown

• The ROW function finds the row number of a cell reference.
• The XLOOKUP function looks up an item in an array.
• Press Enter.
• You can see the complete Repeated Items column.

## Practice Section

You can download the following Excel file and practice the explained methods.

## Things to Remember

After getting the repeated rows you can easily copy-paste them into other places.

The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function never searches for the data on the left.

## Related Articles

<< Go Back to Repeat in Excel | Excel Cell FormatÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

1. Sorry but apart from the manual do it your self method the rest fails. At least on my laptop here in Sweden.

The VBA give this error Run-time error ‘1004’:
Application-defined error

2. Agree with STEEN

3. Hello STEEN and DAV,
I have checked the methods on several laptops. They are working well here.
For your convenience, I have added a new Excel file with the VBA code.