# How to Repeat Multiple Rows in Excel – 4 Methods

### Method 1 – Repeat Multiple Rows Using the Fill Handle Tool

Steps:

In the following dataset you need to repeat rows 5 to 7.

### Method 2 – Copy Multiple Rows Using Formula

Steps:

• Enter the following formula in B8. Drag the Fill Handle to the right.
`=IF(ISBLANK(INDIRECT("Formula!B"&ROW(B5))),INDIRECT("Formula!B"&(ROWS(\$B\$5:B5)-(COUNTA(Formula!B:B)-2))),Formula!B5)`

• Drag down the Fill Handle down to repeat rows.

Formula Breakdown

ROW(B5)
The ROW function returns the row number of a cell.
Output: {5}

ROWS(\$B\$5:B5)
The ROWS function returns the number of rows in a reference or array.
Output: 1
The output will change as you drag the formula down.

COUNTA(Formula!B:B)
The COUNTA function counts the number of non-blank cells in a range.
Output: 6

INDIRECT(“Formula!B”&ROW(B5))
The INDIRECT function returns the reference specified by a text string.
Output: {“Amelia Claire”}

ISBLANK(INDIRECT(“Formula!B”&ROW(B5)))
The ISBLANK function checks whether a reference is an empty cell and returns TRUE or FALSE.
Output: {FALSE}

IF(ISBLANK(INDIRECT(“Formula!B”&ROW(B5))),INDIRECT(“Formula!B”&(ROWS(\$B\$5:B5)-(COUNTA(Formula!B:B)-2))),Formula!B5)
The IF function checks whether a condition is met, and returns one value if TRUE and another if FALSE.
Output: {“Amelia Claire”}

### Method 3 – Repeat Header Rows While Printing

This sample dataset can’t be printed on a single page.

The first page will be printed as shown below.

The following pages will be printed without headers.

Steps:

• Go to the Page Layout tab and select Page Setup.
• Click Print Titles.  .

• Choose Rows to repeat at top: and select the header rows.
• Click OK.

Header rows will be printed on each page.

### 4. Repeat Multiple Rows Using VBA

Steps:

In this sample dataset enter the numbers by which you want to repeat rows in a new adjacent column.

• Press ALT+F11 to open the VBA window. Next, select Insert >> Module.

• Enter the following code.
``````Sub RepeatMultipleRows()
Dim rng, crng As Range
Dim fnum, rn As Integer
On Error Resume Next
SelectRange:
Set rng = Application.InputBox("Select the repeating number", _
"ExcelDemy.com", xTxt, , , , , 8)
If rng Is Nothing Then Exit Sub
If rng.Columns.Count > 1 Then
MsgBox "Select single column only!"
GoTo SelectRange
End If
Application.ScreenUpdating = False
For fnum = rng.Count To 1 Step -1
Set crng = rng.Item(fnum)
rn = CInt(crng.Value)
With Rows(crng.Row)
.Copy
.Resize(rn).Insert
End With
Next
Application.ScreenUpdating = True
End Sub``````
• Paste the copied code into the blank module.

• Press F5 to run the code.

• Select the range containing the specific numbers and click OK.

Each row is repeated the specified number of times.

VBA Code Breakdown

Sub RepeatMultipleRows()
Creates a sub-procedure.

Dim rng, crng As Range
Dim fnum, rn As Integer
Declares necessary variables.

On Error Resume Next
Forces VBA to ignore errors.

SelectRange:
Set rng = Application.InputBox(“Select the repeating number”, _
“ExcelDemy.com”, xTxt, , , , , 8)
Takes user input.

If rng Is Nothing Then Exit Sub
If the user enters nothing, the subject ends.

If rng.Columns.Count > 1 Then
MsgBox “Select single column only!”
If the user selects more than one column, VBA shows a warning.

GoTo SelectRange
VBA repeats the earlier process.

Application.ScreenUpdating = False
Forces VBA to run in the background to work faster.

For fnum = rng.Count To 1 Step -1
Set crng = rng.Item(fnum)
rn = CInt(crng.Value)
With Rows(crng.Row)
.Copy
.Resize(rn).Insert
End With
Next
Copies the rows the specified number of times by using the rn variable.

Application.ScreenUpdating = True
Resets screen updating.

## Things to Remember

• Back up your data before running the VBA code.
• Save the file as a macro-enabled workbook to avoid losing the code.

## Related Articles

<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

1. After press F5 the box with “run” not appear straight to key in the number of repeat.
When i highlight the column of repeat number it not show something like \$A\$1：\$A\$22

• Hello KHOR,
After pressing F5 I am having the input box with the help of which I could select the range of repeated numbers easily and perform the row repetition. But if this shortcut key is not working for you then you can try the following technique.
• Press the Run button above your code.

Then, the input box will appear.
• Go to the main sheet and select your range containing numbers up to which you want to repeat.

• After pressing OK, you will get the work done.

Thanking You
Tanjima Hossain
ExcelDemy

2. Hi Shameem,
Thank you so much for the code and I wanted to mention series of numbers for every cell. For example I have copied 50 columns by using the code and need to mention 1 to 50 numbers in each cell. Do we have any possibility to extend the code? If yes, please help me.

Lutfor Rahman Shimanto Feb 5, 2024 at 6:15 PM

Hello BHARATH L

Thanks for your nice words. Your appreciation means a lot to us. You wanted to mention a series of numbers for every copied row. In your words, if you have copied 50 columns using the code, you need to mention 1 to 50 numbers in each row.

I am delighted to inform you that I have developed an Excel VBA sub-procedure to fulfil your requirements. I have modified the existing article code and made it an advanced version.

OUTPUT OVERVIEW:

``````
Sub RepeatMultipleRows()

Dim rng As Range, crng As Range
Dim fnum As Integer, rn As Integer
Dim i As Integer
On Error Resume Next

SelectRange:
Set rng = Application.InputBox("Select the repeating number", "ExcelDemy.com", xTxt, , , , , 8)

If rng Is Nothing Then Exit Sub
If rng.Columns.Count > 1 Then
MsgBox "Select single column only!"
GoTo SelectRange
End If

Application.ScreenUpdating = False

For fnum = rng.Count To 1 Step -1
Set crng = rng.Item(fnum)
rn = crng.Value
For i = 1 To rn
crng.Offset(0, 1).Resize(1, 1).Value = i
crng.EntireRow.Copy
crng.Offset(1).EntireRow.Insert
Next i
crng.Offset(0, 1).Value = crng.Offset(0, 0).Value + 1
crng.Offset(0, 1).Font.Bold = True
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
``````

Hopefully, the code will fulfil your goal. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

3. Hello LUTFOR RAHMAN SHIMANTO,

Thank you for the above and it is working awesome.

Best Regards
Bharath L

• Dear Bharath L,

You are most welcome. We are glad that our solution worked for you.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF