Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Copy and Insert Row Multiple Times or Duplicate the Row X Times in Excel?
If we want to duplicate the existing rows in a table in Excel when we do it manually, then it can be a time-consuming process as we need to insert and copy the values. We can automate this process using the VBA application.
Read this tutorial to learn how you can copy and insert a row multiple times, or duplicate the row "X" times, in Excel. Here we will first insert the VBA module and then run the code to complete the task. Let's look at a simple procedure for copying and inserting a row multiple times or duplicating a row "X" times in Excel.
Step 1
Let us consider an Excel sheet that has a table similar to the one below.
To open the VBA application, click on insert, select view code, click on insert, select module, and type the below-mentioned program1 into the text box as shown in the below image.
Program 1
Sub test()
'Update By Nirmal
Dim xCount As Integer
LableNumber:
xCount = Application.InputBox("Number of Rows", "Duplicate the rows", , , , , , 1)
If xCount < 1 Then
MsgBox "the entered number of rows is error, please enter again", vbInformation, "Select the values"
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
Step 2
Now save the sheet as a macro-enabled worksheet, click the value you want to duplicate, then click F5, select the number of times you want to duplicate, and click OK.
If we want to duplicate the whole row, we can use program 2 instead of program 1
Program 2
Sub insertrows()
'Update By Nirmal
Dim I As Long
Dim xCount As Integer
LableNumber:
xCount = Application.InputBox("Number of Rows", "Duplicate whole values", , , , , , 1)
If xCount < 1 Then
MsgBox "the entered number of rows is error ,please enter again", vbInformation, "Enter no of times"
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
Conclusion
In this tutorial, we used a simple example to demonstrate how you can copy and paste a row or column multiple times in Excel.
12K+ Views
