This article illustrates how to repeat multiple rows in excel. If you are working with datasets, then typing the same data, again and again, is both tiresome and time-consuming. Fortunately, Excel allows us to repeat the same data very quickly. In this article, we will discuss how to repeat multiple rows in 4 different ways in excel. Follow along to be able to do that.

## Download Practice Workbook

You can download the practice workbook from the download button below.

## 4 Methods to Repeat Multiple Rows in Excel

Here are the 4 ways you can repeat multiple rows in excel.

### 1. Repeat Multiple Rows Using Fill Handle Tool

You can use the **Fill Handle** tool to quickly repeat multiple rows in excel.

**📌 Steps:**

- Assume you have the following dataset. You need to repeat rows 5 to 7.

- Then, select the rows and drag the
**fill handle**icon below. After that, you will get the desired results.

**Read More:** **How to Repeat Rows in Excel at Bottom (5 Easy Ways)**

### 2. Copy Multiple Rows Using Formula

Alternatively, you can use a custom formula to do that.

**📌 Steps:**

- First, apply the following formula in cell
**B8**. Then, drag the**fill handle**icon to the right. Now the first row is being repeated once.

`=IF(ISBLANK(INDIRECT("Formula!B"&ROW(B5))),INDIRECT("Formula!B"&(ROWS($B$5:B5)-(COUNTA(Formula!B:B)-2))),Formula!B5)`

- Next, drag the
**fill handle**down to repeat the rows as many times as you want.

**How Does the Formula Work?**

**➤ 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 to 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 value if FALSE.

Output: {“Amelia Claire”}

**Read More:** **How to Repeat Formula for Each Row in Excel (7 Easy Ways)**

**Similar Readings**

**How to Repeat Formula in Excel for Whole Column (5 Easy Ways)****How to Repeat Column Headings on Each Page in Excel (3 Ways)****[Fixed!] Excel Rows to Repeat at Top Feature Greyed Out****How to Select Column A as Titles to Repeat on Each Page****How to Repeat Cell Value X Times in Excel (6 Easy Methods)**

### 3. Repeat Header Rows While Printing

Now we will discuss a different case of repeating multiple cells in excel.

Assume you have a larger dataset as follows that can’t be printed on a single page.

When you print the sheet, the first page will be printed as follows.

But the next pages will be printed without any header cells.

You can make excel repeat the multiple header rows at the top of every page while printing the sheet.

**📌 Steps:**

- First, go to the
**Page Layout**tab and click on**Print Titles**. You will find that in the**Page Setup**group.

- Then make sure you are in the
**Sheet**tab in the**Page Setup**dialog box. Next, click on the upward arrow in the**Rows to repeat at top:**field to select the header rows at the top of each page. Click OK after that.

- Now, excel will repeat the selected multiple header rows at the top of each page while printing.

**Read More:** **How to Repeat Rows in Excel When Printing (3 Effective Ways)**

### 4. Repeat Multiple Rows Using VBA

Finally, we will discuss how to repeat multiple rows for a specific number of times with VBA in excel.

**📌 Steps:**

- Consider the first example again. Here, first, you need to enter the numbers by which you want to repeat the rows in a new adjacent column as shown below.

- Then press
**ALT+F11**to open the VBA window. Next select**Insert >> Module**. After that, a blank module will be created.

- Now copy the following code.

```
Sub RepeatMultipleRows()
Dim rng, crng As Range
Dim fnum, rn As Integer
On Error Resume Next
SelectRange:
xTxt = ActiveWindow.RangeSelection.Address
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
```

- Next, paste the copied code onto the blank module as below.

- After that, press
**F5**to run the code.

- Then, select the range containing the specific numbers and click OK.

- Finally, you will see each row is being repeated the specified number of times below it.

**🔎 VBA Code Explanation**

**Sub RepeatMultipleRows()**

We will write the code inside this subject procedure.

**Dim rng, crng As Range****Dim fnum, rn As Integer**

Declaring necessary variables.

**On Error Resume Next**

Force VBA to ignore errors.

**SelectRange:****xTxt = ActiveWindow.RangeSelection.Address****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 then 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**

Then, 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 number of times specified by the **rn **variable.

**Application.ScreenUpdating = True**

Resets screen updating.

**Read More:** **How to Repeat Rows in Excel When Scrolling (6 Suitable Ways)**

## Things to Remember

- You can copy the repeated rows and paste them as values if required. But, don’t forget to copy the formula before doing that.
- Back up your data before running the VBA code.
- You need to save the file as a macro-enabled workbook to avoid losing the code.

## Conclusion

Now you know how to repeat multiple rows in excel in 4 different ways. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our **ExcelDemy** blog to explore more about excel. Stay with us and keep learning.

**Related Articles**

**Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)****How to Autofill in Excel with Repeated Sequential Numbers****How to Repeat Formula Pattern in Excel (Easiest 8 ways)****Repeat Text in Excel Automatically (5 Easiest Ways)****How to Repeat Number Pattern in Excel (5 Methods)****How to Count Repeated Words in Excel (11 Methods)**

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

F5I 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

Runbutton 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.Hope this way will help you solve your problem.

Thanking You

Tanjima HossainExcelDemy