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.


📂  Download Excel File

Download free workbook from here:


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.

Dataset to repeat rows a specified number of times in EXcel


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.

Using the Fill Down feature to repeat rows a specified number of times in Excel

  • Consequently, you will get the desired result as well.

Result of using the fill-down feature to repeat rows a specified number of times in Excel

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

📕 Read More: 5 Ways to Fill Down to Last Row with Data 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.

Applying the Fill Handle icon to repeat rows a specified number of times in Excel

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

Result of using the Fill Handle icon to repeat rows a specified of times in Excel

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.

Elementary step to use the VLOOKUP function to repeat rows a specified number of times

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

Using the VLOOKUP function to repeat rows a specified number of times

  • Now use the formula for the rest of the cells.

Result of Using the VLOOKUP function to repeat rows a specified number of times

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

the VBA code to repeat rows a specified number of times

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

$B$6:$C$13

  • Now click OK.

Selecting the range for the VBA code

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

Result of Using the VBA code to repeat rows a specified number of times

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

📕 Read More: 6 Ways to Fill Down to Last Row with Data Not Blank 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.


Related Articles

(Visited 94 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo