Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
Register  \/ 


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.
doc repeat rows based on column 1

Repeat rows based on another column value with VBA

Repeat rows with a fixed time 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()
Dim xRg As Range
Dim xCRg As Range
Dim xFNum As Integer
Dim xRN As Integer
On Error Resume Next
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)
    End With
Application.ScreenUpdating = True
End Sub

doc repeat rows based on column 2

3. Press F5 key, a dialog pops out, select the number list which the rows will be repeated based on.
doc repeat rows based on column 3

4. Click OK, the rows have been repeated based on the selected column value.
doc repeat rows based on column 1

Repeat rows with a fixed time by Kutools for Excel

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.
doc repeat rows fixed time 1

Kutools for Excel, with more than 300 handy functions, makes your jobs more 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.
doc repeat rows fixed time 2

2. Click Ok, the blank rows have been inserted below each row.
doc repeat rows fixed time 3

3. Keep the range selected, click Kutools > Insert > Fill Blank Cells. In the Fill Blank Cells dialog, check Based on values and Down options.
doc repeat rows fixed time 4

4. Click Ok. Now the blank cells have been filled with above value.
doc repeat rows fixed time 5

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished. · 2 years ago
    Hi, how to move the repeated output to the side of the original data?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Hi,yiyun zhu, you can make a copy of the original data, then use above method to handle the copy one, and keep the original data .