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.

Step 4

Press F5 to run the macro and execute the code.

Have Your Say

Δ