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

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel we may need to repeat rows a specific number of times. This happens when you are making bills for products or keeping records. Excel has many features by which we can repeat rows a specified number of times. Today in this article, we will demonstrate 5 methods to repeat rows a specified number of times in Excel.

## 5 Suitable Ways to Repeat Rows for a Specified Number of Times in Excel

Consider a situation where you are given a dataset containing columns Item, their Grade, and Stock. You have to repeat some of its rows to, make a bill. In this article, we’ll discuss 5 different ways to repeat rows a specified number of times. Here, we used Excel 365. You can use any available Excel version. ### 1. Using Fill Feature to Repeat Rows for a Specified Number of Times in Excel

In this method, we will use the Fill Feature to repeat rows a specified number of times in Excel. We can use the Fill feature in two ways. Let’s discuss them.

#### 1.1. Using Fill Handle Tool

One of the easiest ways to repeat rows a specified number of times is to use the Fill Handle tool.

Steps

• In the first place, select the whole row that you need to repeat a specified number of times.
• Afterward, hover over your mouse to the bottom right corner of the cell until you see the fill handle icon (+).
• Then, when you see the icon, stop moving your mouse and click and drag the icon to repeat rows. • After dragging the specified number of cells, stop dragging and release the mouse. The rows are repeated perfectly! #### 1.2. Employing Fill Feature from Ribbon

The Fill feature from the ribbon of Excel is also useful when you want to repeat the rows. Let’s see how it works!

Steps

• First of all, select the row that is under the row we want to repeat.
• Here, we want the cells B11:D11 to be repeated.
• Therefore, we selected a row under this cell by selecting cells B12:D12.
• Then, go to your Home Tab >> from the Editing group >> click on Fill.
• Furthermore, from the available options, click on Down. • As a result, you can see the repeated row. • In a similar way, we repeat rows a specified number of times. ### 2. Using VLOOKUP Function

In the following dataset, you can see the Item and Repeat Time columns. In the Repeat Time column, we mention the number of times we want the rows to be repeated. Next, we will use the VLOOKUP function to repeat rows a specified number of times. Steps

• In the first place, create a new column named the Helper Column.
• Then, we put 1 in cell B5. • After that, we will type the following formula in cell B6.
`=B5+D5` • Moreover, press ENTER.
• Therefore, you can see the result in cell B6.
• Furthermore, we will drag down the formula with the Fill Handle tool. • Therefore, you can see the complete Helper Column. •  After that, we make another column and name it Column 2.
• Then, we enter 1 to 15 in cells E5 to E20. • Afterward, we add a column named Repeat.
• Then, we type 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
•  At this point, press ENTER.
• Therefore, you can see the result in cell F5.
• Moreover, we will drag down the formula with the Fill Handle tool. • Hence, you can see the complete Repeat column. ### 3. Inserting VBA Code to Repeat Rows for a Specified Number of Times in Excel

In this method, we will insert a VBA code to repeat rows a specified number of times in Excel.

Steps

• In the first place, we will go to the Developer tab >> select Visual Basic. • At this point, a VBA Editor window will appear.
• Then, from the Insert tab >> we will select Module. • Then, we will type 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

• Here, we declare 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.
• After that, we Save the code >> return to our Worksheet.
• Now, to run the code, we go to the Developer tab >> select Macros. • Moreover, we select the Sub >> click on Run. • At this point, a prompt box appears where you have to input the range (\$B\$5:\$B\$10).
• In addition, click OK to continue. • Along with that, another prompt box appears >> select a cell where you want to show your output.
• Here, we selected cell E5.
• In addition, click OK to continue. • As a result, we have got our specified number of repeated rows. ### 4. Combining IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA Functions

Here, we will use the combination of IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA functions to repeat rows a specified number of times in Excel.

Steps

• First of all, we put the items in the Item sheet. • Then, in the Repeat sheet, we want these items to be repeated in the empty cells of the following created table. • Furthermore, we will type 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
• At this point, press ENTER.
• Therefore, you can see the result in cell B11.
• Furthermore, we will drag the formula towards the right through the Fill Handle tool. • In addition, we will drag down the formula with the Fill Handle tool. •  As a result, you can see the number of repeated rows. ### 5. Merging TRIM, TEXTJOIN, IFERROR, FILTERXML, REPT, SUBSTITUTE, and XLOOKUP Functions

In the following dataset, you can see the item and Repeat Time columns. Here, we will use the TRIM, TEXTJOIN, IFERROR, FILTERXML, REPT, ROW, SUBSTITUTE, and XLOOKUP functions to repeat rows a specified number of times in Excel. Steps

• First, we will type 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
•  After that, press ENTER.
• Therefore, you can see the result in cell E4. • Then, we will add a Helper 2 column.
• Furthermore, we will type 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.
• Afterward, press ENTER.
• Therefore, you can see the complete Helper 2 column. • Next, we add a Repeated Items column.
• Then, we type 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.
• Next, press ENTER.
• Therefore, 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.

## Conclusion

Here, we show you 5 easy examples to repeat rows a specified number of times in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section. You can visit our website Exceldemy for more related articles.

## Related Articles Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

1. Reply 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. Reply Agree with STEEN

3. Reply 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.
Thank you. Have a good day.

4. Reply Mind Blowing, I’m surprised and this formula wawo Advanced Excel Exercises with Solutions PDF  