Duplicate Excel cells multiple 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.
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
Open the Excel sheet that needs to be modified.
Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.
In the Visual Basic for Applications toolbar, go to Insert > Module
Insert the above code into the module.
Press F5 to run the macro and execute the code.