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 the next row as following screenshot shown. How could you deal with this job in Excel?


Copy and insert each row multiple times based on a specific number easily

Normally, there is not a good method for copying and inserting rows multiple times except dealing with manually copy and insert. But, with Kutools for Excel's Duplicate Rows / columns based on cell value feature, you can solve this problem with ease. Click to download Kutools for Excel!

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


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
    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 to enter the number of times that you want to duplicate, see screenshot:

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


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
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 2 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 to enter the number of times that you want to duplicate for each record, see screenshot:

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

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


Copy and insert each row multiple times based on a specific number with an awesome feature

Maybe, you are not familiar with the VBA code,or worry about the code will crash your data. Here, I will introduce a useful feature, Kutools for Excel's Duplicate Rows / Columns based on cell value, with this utility, you can quickly copy and insert the rows based on the number you specified.

Tips:To apply this Duplicate Rows / Columns based on cell value feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Enter the repeat numbers that you want to duplicate rows in a list of cells beside your data, see screenshot:

2. Click Kutools > Insert > Duplicate Rows / Columns based on cell value, see screenshot:

3. In the Copy and insert rows & columns dialog box, select Copy and insert rows option in the Type section,  then select the data range you want to duplicate, and then specify the repeat time to duplicate the rows, see screenshot:

4. Then, click Ok or Apply button, you will get the following result as you need:

Click to Download Kutools for Excel and free trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, 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!
officetab bottom
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.
    Ismael · 2 months ago
    No funciona con Windows 2019, deseo usar la función en mención pero simplemente no hace absolutamente nada
  • To post as a guest, your comment is unpublished.
    Malik · 4 months ago
    Copy And Insert Each Row Multiple Times With VBA Code

    In VBA code how can I select the starting row
  • To post as a guest, your comment is unpublished.
    Sal · 5 months 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 · 4 months 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 · 6 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 · 7 months ago
    How can I do this for columns?
  • To post as a guest, your comment is unpublished.
    Jamar · 7 months ago
    how do I do this for columns?
  • To post as a guest, your comment is unpublished.
    Andreea · 11 months ago
    Love it! It works every time! Thank you so much for this :)
  • To post as a guest, your comment is unpublished.
    Sashitharan Govindasamy · 1 years ago
    Hello, I need help, this is how my table looks, for example, when I add 2 row at the end of my 1st table, I want the vba help me to do the same thing for the table below (add 2 rows at the end of the table). So my job will be, only need to add rows at the 1st table, the table bellows all will generate rows automatically based on numbers of row in the 1st table. the are the coding I used now to add row for the 1st table. I hope someone can solve this issue for me, thx.Sub InsertNumRows()

    ActiveCell.EntireRow.Copy

    Dim Rng As Long

    Application.DisplayAlerts = False
    On Error Resume Next
    Rng = InputBox("Enter number of rows required.")
    On Error GoTo 0
    Application.DisplayAlerts = True

    If Rng = 0 Then
    MsgBox "You didn't specify a range!"
    Exit Sub

    Else
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(Rng, 0)).Select
    Selection.EntireRow.Insert Shift:=xlDown
    End If

    End Sub
  • To post as a guest, your comment is unpublished.
    devin · 1 years ago
    Love the code! how do i set the start range to skip the first row (Column Headers) and start on row 2?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Devin,
      To skip the first header row, please apply the following VBA code:

      Sub insertrows()
      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 2 Step -1
      Rows(I).Copy
      Rows(I).Resize(xCount).Insert
      Next
      Application.CutCopyMode = False
      End Sub

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    naz · 1 years ago
    Really thanks for your support !!!!
  • To post as a guest, your comment is unpublished.
    May · 1 years ago
    Hi, how can I change the code to include the following

    1. duplicate based on number of days
    2. change column EOM from end of month to daily date
  • To post as a guest, your comment is unpublished.
    Talen · 1 years ago
    I used this macros in the past and it has always worked until lately. Any insight why it would only duplicate the top row even when using the multiple row macros? I have removed all filters/frozen panes.
  • To post as a guest, your comment is unpublished.
    Adnan · 1 years ago
    I want to duplicate rows based on a parameter. For example, I have a column named meeting duration time. If that column is more than 1, I want to duplicate meeting ID rows based on that number. If meeting duration is 2 hours, then duplicate meetingID two times. If meeting is 3 hours then duplicate three times.
    half an hour increments can be roundup to the hour.

    MeetingID Time of Start Time of End Meeting Duration
    43117 9:00 AM 11:00 AM 02:00
    43580 9:30 AM 11:00 AM 01:30
    42699 10:00 AM 11:30 AM 01:30
    12345 01:00 PM 2:00 PM 01:00
    • To post as a guest, your comment is unpublished.
      Erin · 1 years ago
      I have the same problem. Have you found a solution?
      • To post as a guest, your comment is unpublished.
        skyyang · 1 years ago
        Hi, guys,
        May be the following VBA code can do you a favor, please apply it:
        Sub insertrows()
        Dim I As Long
        Dim xRg As Range
        Dim xHour, xMin As Long
        On Error Resume Next
        Application.ScreenUpdating = False
        For I = Range("D" & Rows.CountLarge).End(xlUp).Row To 1 Step -1
        xHour = 0
        Set xRg = Range("D" & I)
        xHour = Hour(xRg.Value) + 1
        xMin = Minute(xRg.Value)
        If xMin < 30 Then xHour = xHour - 1
        Rows(I).Copy
        Rows(I).Resize(xHour).Insert
        Next
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
        End Sub

        Please try it. Hope it can help you!
  • To post as a guest, your comment is unpublished.
    Rushabh Patel · 1 years ago
    i want to generate duplicate rows randomly,

    for eg. generate random duplicate rows between 20 to 80.


    what will be updated code for the following


    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
  • To post as a guest, your comment is unpublished.
    mate · 1 years ago
    real nice macro mate!
  • To post as a guest, your comment is unpublished.
    BJ Conley · 1 years ago
    Awesome Macro - Saved me so much time!
  • To post as a guest, your comment is unpublished.
    Zach · 2 years ago
    Thank you!