Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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:
would become:

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

Screen capture of Visial Basic for Applications

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.



No comments have yet been submitted. Be the first!

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.