How to repeat rows a specified number of times based on another column in Excel?
Have you ever tried to repeat a row based on another column value as below screenshot shown? In this article, I will introduce the method on solving this job in Excel.
Repeat rows based on another column value with VBA
Repeat rows based on another column value by Kutools for Excel
Repeat rows based on another column value with VBA
Here I have a code which can quickly repeat the rows based on the last column values.
1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.
2. Click Insert > Module, then copy below code and paste it to the new Module script.
VBA: Repeat rows based on another column value
Sub CopyRow()
'UpdatebyExtendoffice20181011
Dim xRg As Range
Dim xCRg As Range
Dim xFNum As Integer
Dim xRN As Integer
On Error Resume Next
SelectRange:
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Select the number value", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count > 1 Then
MsgBox "Please select single column!"
GoTo SelectRange
End If
Application.ScreenUpdating = False
For xFNum = xRg.Count To 1 Step -1
Set xCRg = xRg.Item(xFNum)
xRN = CInt(xCRg.Value)
With Rows(xCRg.Row)
.Copy
.Resize(xRN).Insert
End With
Next
Application.ScreenUpdating = True
End Sub
3. Press F5 key, a dialog pops out, select the number list which the rows will be repeated based on.
4. Click OK, the rows have been repeated based on the selected column value.
Repeat rows based on another column value by Kutools for Excel
If you are not good at using VBA, you can try Kutools for Excelās Duplicate Rows/Columns Based on Cell Value feature, which just needs two steps.
1. Click Kutools > Insert > Duplicate Rows/Columns Based on Cell Value.
2. In the popping dialog, choose Copy and insert rows option in the Type section, then choose the range that you want to repeat to Insert Range textbox, and choose the column that decides the repeat times to the Repeat Times textbox. Click Ok.
Then the rows will be repeated by the selected column.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.





- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in