Duplicate Excel cells multiple times
Use this Visual Basic script in Microsoft Excel to duplicate cells a specified number of times.
In order for me to enter data into a Microsoft Excel database for a particular job, I had to enter numbers twice. This became tedious; I wanted a method to enter the numbers into Excel once and then have Excel duplicate them for me in the order that they were entered.
For example:
“A”
“B”
“C”
would become:
“A”
“A”
“B”
“B”
“C”
“C”
The following script, inspired by brettdj, enables numbers to be entered into cells in Column A of a spreadsheet and will then duplicate them x times in order:
Sub DuplicateRows()
Dim X
Dim Y
Dim strDelim As String
Dim lngRepeat As Long
strDelim = ","
lngRepeat = 2 'Specify the number of duplicates (eg: 2 = 1 original + 1 duplicate)
Y = Split(Replace(Join(Application.Transpose(Range([A1], Cells(Rows.Count, "A").End(xlUp))), strDelim), strDelim, "|" & strDelim), strDelim)
Y(UBound(Y)) = Y(UBound(Y)) & "|"
X = Replace(Replace(Join(Application.Rept(Y, lngRepeat), strDelim), "|", strDelim), strDelim & strDelim, strDelim)
[A1].Resize((UBound(Y) - LBound(Y) + 1) * lngRepeat, 1) = Application.Transpose(Split(X, strDelim))
End Sub
The script can be customised to specify how many duplicates need to be inserted. Simply change lngRepeat = 2
.
Activating the script
Step 1
Open the Excel sheet that needs to be modified.
Step 2
Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.
Step 3
In the Visual Basic for Applications toolbar, go to Insert > Module

Inserting a module in Visual Basic for Applications.
Insert the above code into the module.
Step 4
Press F5 to run the macro and execute the code.
Comments
No comments have yet been submitted. Be the first!