While working in Excel we may need to repeat rows a specific number of times. This happens when you are making bills of 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 some of the methods to repeat rows a specified number of times in Excel.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
4 Suitable Ways to Repeat Rows 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 four different ways to repeat rows a specified number of times.
1. Apply the Fill Handle Feature to Repeat Rows a Specified Number of Times in Excel
One of the easiest ways to repeat rows a specified number of times is to use the Fill Handle feature. To apply that feature, follow these steps.
- Select the whole row that you need to repeat a specified number of times.
- Hover over your mouse to the bottom right corner of the cell until you see the fill handle icon (+).
- 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!
2. Employ the Fill Feature to Repeat Rows a Specified Number of Times in Excel
The Fill feature of Excel is also useful when you want to repeat the rows. Let’s see how it works!
- Select the number of rows that you want to repeat.
- Go to your Home Tab and click on Fill from Editing Ribbon. From the available options, click on Down.
- And our rows are repeated as per our given numbers!
- How to Fill Down to Last Row with Data in Excel (3 Quick Methods)
- How to Use Autofill Formula in Excel (6 Ways)
- How to Merge Rows in Excel (4 Ways)
3. Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel
The VLOOKUP function can help you to repeat rows a specified number of times. Learn this method by following these steps!
- Create two new columns named the Helper Column and the Repeat Time.
- In the Repeat Time column, you mention the number of times you want the rows to be repeated.
- In the Helper Column, we will add a formula for the VLOOKUP function to use.
- In the B5 cell of the Helper Column, insert this formula.
- Press Enter and repeat the same formula to the end of the cells.
- Make another column and name it Column 2.
- Enter 1 in G4 of Column 2 and fill the number by using the fill handle feature to 15 that is gross number of times mentioned in the Repeat Time.
- Insert a new column named Repeat. In Cell H4 of the Repeat Column, apply the VLOOKUP After inserting the values into the function, the final form is,
- Here lookup_value is G4, lookup_array is $B$3:$E$9 and col_Index_num is 2.
- Press Enter to get the result.
- Now apply the same formula to the rest of the cells. The Rows are repeated the specified number of times which is mentioned in the column.
4. Insert VBA codes to Repeat Rows a Specified Number of Times in Excel
VBA codes can help you to repeat your rows a specified number of times. Let’s see how!
- Copy your dataset to a new worksheet and create a column named Product.
- Press Alt+F11 to open the VBA
- In the VBA window click on Insert and select Module to open a new module.
- You will write the VBA codes in the new module. We have given the code below. You can Just Copy and Paste the code.
Sub RepeatData() 'Repeat Rows Dim XRg As Range Dim InRg As Range, OutXRg As Range xTitleId = "Repeat Rows" 'A Box Named Repeat Rows will Show Set InRg = Application.Selection Set InRg = Application.InputBox("Range :", xTitleId, InRg.Address, Type:=8) Set OutXRg = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set OutXRg = OutXRg.Range("A1") For Each XRg In InRg.Rows xValue = XRg.Range("A1").Value xNum = XRg.Range("B1").Value OutXRg.Resize(xNum, 1).Value = xValue Set OutXRg = OutXRg.Offset(xNum, 0) Next End Sub
- After writing the codes, click on Run to run the code.
- A prompt box appears where you have to input the range ($B$4:$C$9). Click OK to continue
- Select a cell where you want to show your output ($E$4). Click OK to continue.
- We have got our specified number of repeated rows.
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.
Repeating rows using four different methods is discussed in this article. We hope this article proves useful to you. Do comment if you have any questions or queries.