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.
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.
If you want to repeat rows with a fixed time without inserting rows one by one, you can try to use the Insert Blank Rows & Columns and Fill Blank Cells utilities of Kutools for Excel to quickly handle this job.
|Kutools for Excel, with more than 200 handy functions, makes your jobs easier.|
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the data range you want to repeat rows, click Kutools > Insert > Insert Blank Rows & Columns. Then in the Insert Blank Rows & Columns dialog, check Blank rows option, then type 1 into Interval of textbox, and type the number you want to repeat rows in Rows textbox.
Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 45-day trial of Office Tab!