Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)

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.

Apply the Fill Handle Feature to Repeat Rows a Specified Number of Times in Excel


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.

Step 1:

  • 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 (+).

Apply the Fill Handle Feature to Repeat Rows a Specified Number of Times in Excel

  • 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!

Apply the Fill Handle Feature to Repeat Rows a Specified Number of Times in Excel


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!

Step 1:

  • 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.

Employ the Fill Feature to Repeat Rows a Specified Number of Times in Excel

  • And our rows are repeated as per our given numbers!

Employ the Fill Feature to Repeat Rows a Specified Number of Times in Excel


Similar Readings:


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!

Step 1:

  • 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.

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

Step 2:

  • In the B5 cell of the Helper Column, insert this formula.
=B4+F4

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

  • Press Enter and repeat the same formula to the end of the cells.

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

Step 3:

  • 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.

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

  • 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,
=VLOOKUP(G4,$B$3:$E$9,2)
  • Here lookup_value is G4, lookup_array is $B$3:$E$9 and col_Index_num is 2.

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

  • Press Enter to get the result.

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel

  • 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.

Use the VLOOKUP Function to Repeat Rows a Specified Number of Times in Excel


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!

Step 1:

  • Copy your dataset to a new worksheet and create a column named Product.
  • Press Alt+F11 to open the VBA

Insert VBA codes to Repeat Rows a Specified Number of Times in Excel

Step 2:

  • In the VBA window click on Insert and select Module to open a new module.

Insert VBA codes to Repeat Rows a Specified Number of Times in Excel

  • 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.

Step 3:

  • 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.


Conclusion

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.


Similar Articles to Explore

Asikul Himel
1 Comment
  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

Leave a reply

ExcelDemy
Logo