Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to copy and insert row multiple times or duplicate the row x times in Excel?

In your daily work, have you ever tried to copy a row or each row and then insert multiple times below the current data row in a worksheet? For example, I have a range of cells, now, I want to copy each row and paste them 3 times to next row as following screenshot shown. How could you deal with this job in Excel?

doc copy insert x times 1

Copy and insert a specific row multiple times with VBA code

Copy and insert each row multiple times with VBA code

Copy and insert a specific row for every other or nth row with Kutools for Excel


arrow blue right bubble Copy and insert a specific row for every other or nth row with Kutools for Excel

Copy and insert a specific row for every other or nth row

With Kutools for Excel's Insert Titles Rows feature, you can quickly copy and insert a specific row for every other or nth row at once.

doc copy insert x times 5

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Copy and insert a specific row multiple times with VBA code

If you want to duplicate only one specific row x times, the following VBA code may help you, please do as this:

1. Specify a row which you want to copy and insert multiple times, and then hold down the ALT + F11 keys, then it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Duplicate one specific row multiple times:

Sub test()
'Updateby Extendoffice 20160616
    Dim xCount As Integer
LableNumber:
    xCount = Application.InputBox("Number of Rows", "Kutools for Excel", , , , , , 1)
    If xCount < 1 Then
        MsgBox "the entered number of rows is error, please enter again", vbInformation, "Kutools for Excel"
        GoTo LableNumber
    End If
    ActiveCell.EntireRow.Copy
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(xCount, 0)).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

3. After pasting the code, please press F5 key to run this code, and a prompt box is popped out to remind you enter the number of times that you want to duplicate, see screenshot:

doc copy insert x times 2

4. Then click OK button, and four new copied rows have been inserted below the selected row, see screenshot:

doc copy insert x times 3


arrow blue right bubble Copy and insert each row multiple times with VBA code

To duplicate each rows multiple times in a range, you can apply the following VBA code, please do as below:

1. Hold down the ALT + F11 keys, then it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Duplicate each row multiple times:

Sub insertrows()
'Updateby Extendoffice 2016616
    Dim I As Long
    Dim xCount As Integer
LableNumber:
    xCount = Application.InputBox("Number of Rows", "Kutools for Excel", , , , , , 1)
    If xCount < 1 Then
        MsgBox "the entered number of rows is error ,please enter again", vbInformation, "Kutools for Excel"
        GoTo LableNumber
    End If
    For I = Range("A" & Rows.CountLarge).End(xlUp).Row To 1 Step -1
        Rows(I).Copy
        Rows(I).Resize(xCount).Insert
    Next
    Application.CutCopyMode = False
End Sub

3. And then press F5 key to run this code, and a prompt box is popped out to remind you enter the number of times that you want to duplicate for each record, see screenshot:

doc copy insert x times 4

4. Then click OK, and each row has been copied and inserted 3 times below the active ones, see screenshot:

doc copy insert x times 1

Note: In the above code, the A indicates the data range is started at the column A, if your data starts at column K, please change A to K as your need.



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sal · 19 days ago
    hi this worked great, only i need two rows copied N number of times. Can you please adjust the code so when i select two rows, it will then copy those two rows down N times ? thanks for your help
    • To post as a guest, your comment is unpublished.
      skyyang · 8 days ago
      Hello, Sal,
      If you need to only copy two rows down by multiple times, you just need to use the VBA code 1 in this article, apply the code two times will solve your problem. Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Deep Saha · 1 months ago
    how to perform the same action with selected cells using VB,


    I need to repeat a few selected cells (rows and columns) x no of times in the same sheet and in the same order as:

    line1
    line2
    line3

    line1
    line2
    line3
  • To post as a guest, your comment is unpublished.
    Jamar · 3 months ago
    How can I do this for columns?
  • To post as a guest, your comment is unpublished.
    Jamar · 3 months ago
    how do I do this for columns?
  • To post as a guest, your comment is unpublished.
    Andreea · 6 months ago
    Love it! It works every time! Thank you so much for this :)