4 Ways to Repeat Rows a Specified Number of Times in Excel

Sometimes we need to repeat rows a specified number of times in Excel. In this article, we are going to show you how to repeat rows a specified number of times in Excel with 4 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

Contents hide

📂 Download Excel File

Learn to Repeat Rows a Specified Number of Times in Excel

1. Using Fill Down Feature

2. Dragging Fill Handle Icon

3. Adopting the VLOOKUP Function

4. Using VBA Code to Repeat Rows a Specified Number of Times in Excel

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📂 Download Excel File

Download free workbook from here:

Repeat Rows a Specified Number of Times.xlsm

Learn to Repeat Rows a Specified Number of Times in Excel

While writing this article, “Excel Repeat Rows a Specified Number of Times”, we’ve considered a dataset that contains approximately 4 columns and 13 rows. In this dataset, we added Name, Class, and Points to make you better understand. But if you want, you can change the entities and put your own values.

APPROACH

1. Using Fill Down Feature

You can repeat rows a specified number of times in Excel by using the Fill option. You just need to follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

Initially, select cells B13 to D18.
Secondly, click the Fill option.
Now choose the Down option.

Consequently, you will get the desired result as well.

Undoubtedly, using the Fill option is one of the easiest methods in Excel.

APPROACH

2. Dragging Fill Handle Icon

You may use the Fill Handle manually to repeat rows a specific number of times in Excel. It is a very easy and handy procedure. You can easily do it by applying the following approaches.

⬇️⬇️ STEPS ⬇️⬇️

Primarily, select the cells B13 to D13.
Secondly, apply the Fill Handle.

Now drag the Fill Handle to cells B13 to D18.
Eventually, you will see the result as well.

Truly, applying the Fill Handle manually in Excel is a very easy and short technique.

APPROACH

3. Adopting the VLOOKUP Function

To use the VLOOKUP function, you need to add two columns named Helping and Repeat. Then you need to follow the given steps below.

⬇️⬇️ STEPS ⬇️⬇️

Initially, select cell B7.
Secondly, write the following formula on that cell.

=B6+F6

Now tap Enter.

Using the Fill Handle, drag the formula to the entire column.

Thirdly, the gross number of times specified in the Repeat column is 15, so enter 1 in Column 2‘s H6 and fill the number to 15 using the Fill Handle icon.

Create a new column called Repeat 2. Use the VLOOKUP function in Cell I6 of the Repeat 2

=VLOOKUP(H6,$B$5:$E$13,2)

🔨 Formula Breakdown

👉 Here lookup_value is H6, and column_Index_number is 2.

👉 lookup_array is $B$6:$E$13.

The final form after inserting the values into the function is-

Now use the formula for the rest of the cells.

Lastly, this is a very handy process in Excel.

📕 Read More: 4 Ways to Add Excel Sequence Number by Group

APPROACH

4. Using VBA Code to Repeat Rows a Specified Number of Times in Excel

There is another process to repeat rows a specified number of times in Excel which we know as using the VBA code. You need to follow the steps given below.

⬇️⬇️ STEPS ⬇️⬇️

Primarily, go to the Excel worksheet.
Secondly, press Alt+F11.
Now you will get a VBA window.
Thirdly, insert Module on that window.

Now write the following code there.

Code

Sub Repeat1()
Dim X1Rg As Range
Dim In1Rg As Range, Out1XRg As Range
x1TitleId = "Repeat of Rows"
Set In1Rg = Application.Selection
Set In1Rg = Application.InputBox("Range :", x1TitleId, In1Rg.Address, Type:=8)
Set Out1XRg = Application.InputBox("Out put to (single cell):", x1TitleId, Type:=8)
Set Out1XRg = Out1XRg.Range("A1")
For Each X1Rg In In1Rg.Rows
x1Value = X1Rg.Range("A1").Value
x2Num = X1Rg.Range("B1").Value
Out1XRg.Resize(x2Num, 1).Value = x1Value
Set Out1XRg = Out1XRg.Offset(x2Num, 0)
Next
End Sub

Then click on Run.
Now you will see a dialogue box named Repeat Rows.
Select Range–

$B$6:$C$13

Now click OK.

Again you will see a dialogue box named Repeat Rows.
Select Out put to (single cell)–

$E$6

Finally, click OK.

Eventually, you will find your desired result as well.

Truly, using VBA code is one of the simplest methods in Excel.

📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️ You need to be careful while applying the Fill option.

🖊️ You should apply the VBA code with caution.

🖊️ You should exercise caution when using the Excel functions.

📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌 Firstly, we have used the Fill option to Excel repeat rows a specified number of times.

📌 Secondly, we applied the Fill Handle manually.

📌 Afterward, we adopted the VLOOKUP function.

📌 Finally, we used the VBA code.

Conclusion

We wish that you would be able to repeat rows a specified number of times in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.