Skip to content

Duplicate Excel cells multiple times

G13th April 2016

CNo Comments

TExcel, Visual Basic

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

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.

   

Comments:

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.