How to duplicate rows based on cell value in a column?
For example, I have a range of data which contains a list of numbers in column D, and now, I want to duplicate the entire rows a number of times based on the numeric values in column D to get the following result. How could I copy the rows multiple times based on the cell values in Excel?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To copy and duplicate the entire rows multiple times based on the cell values, the following VBA code may help you, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Duplicate rows multiple times based on cell value:
Sub CopyData() 'Updateby Extendoffice 20160922 Dim xRow As Long Dim VInSertNum As Variant xRow = 1 Application.ScreenUpdating = False Do While (Cells(xRow, "A") <> "") VInSertNum = Cells(xRow, "D") If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "D")).Select Selection.Insert Shift:=xlDown xRow = xRow + VInSertNum - 1 End If xRow = xRow + 1 Loop Application.ScreenUpdating = False End Sub
3. Then press F5 key to run this code, the entire rows have been duplicated multiple times based on the cell value in column D as you need.
Note: In the above code, the letter A indicates the start column of your data range, and the letter D is the column letter that you want to duplicate the rows based on. Please change them to your need.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 5 days agoHi I have tried this but is there a way to consider if there are multiple criteria with the data I am duplicating
To post as a guest, your comment is unpublished.· 5 days agoLovely! Thank you. I wonder if anyone could give a hint on how I would incorporate a new column of information into the table (column E) that is a number of incrementing value for each copied row, 1, 2, 3, 4 etc... and then when it gets to the next item to be duplicated X times, it will start numbering again from 1 and increasing by 1 each time.
To post as a guest, your comment is unpublished.· 2 months agothere is any way we can add to eache repeated cell, a consecutive caracters? example
To post as a guest, your comment is unpublished.· 4 months agoIs there a way to update the module to only duplicate new data? I'm working on an ongoing document and do not want the code to duplicate data that has been previously duplicated.
To post as a guest, your comment is unpublished.· 5 months agohi, for me is no working, I want to remove letters and number duplicate is possible?