While working in Excel we may need to repeat rows a specific number of times. This happens when you are making bills for products or keeping records. Excel has many features by which we can repeat rows a specified number of times. Today in this article, we will demonstrate **5 **methods to repeat rows a specified number of times in Excel.

**Table of Contents**Expand

**How to Repeat Rows for a Specified Number of Times in Excel: 5 Suitable Ways **

Consider a situation where you are given a dataset containing columns **Item**, their **Grade, **and **Stock**. You have to repeat some of its rows to, make a bill. In this article, we’ll discuss **5 **different ways to repeat rows a specified number of times. Here, we used** Excel 365**. You can use any available Excel version.

**1. Using Fill Feature to Repeat Rows for a Specified Number of Times in Excel**

In this method, we will use the **Fill** feature to repeat rows a specified number of times in Excel. We can use the Fill feature in two ways. Let’s discuss them.

#### 1.1. Using Fill Handle Tool

One of the easiest ways to repeat rows a specified number of times is to use the **Fill Handle** tool.

**Steps**

- In the first place, select the whole row that you need to repeat a specified number of times.
- Afterward, hover over your mouse to the bottom right corner of the cell until you see the
**Fill Handle**icon**(+)**. - Then, when you see the icon, stop moving your mouse and click and drag the icon to repeat rows.

- After dragging the specified number of cells, stop dragging and release the mouse. The rows are repeated perfectly!

**Read More: **How to Repeat Rows in Excel at Bottom

#### 1.2. Employing Fill Feature from Ribbon

The **Fill **feature from the ribbon of Excel is also useful when you want to repeat the rows. Let’s see how it works!

**Steps**

- First of all, select the row that is under the row we want to repeat.
- Here, we want the cells
**B11:D11**to be repeated. - Therefore, we selected a row under this cell by selecting cells
**B12:D12**. - Then, go to your
**Home**Tab >> from the**Editing**group >> click on**Fill**. - Furthermore, from the available options, click on
**Down**.

- As a result, you can see the repeated row.

- In a similar way, we repeat rows a specified number of times.

**Read More: **Repeat Text in Excel Automatically

**2. Using VLOOKUP Function**

In the following dataset, you can see the **Item** and **Repeat Time** columns. In the **Repeat Time **column, we mention the number of times we want the rows to be repeated. Next, we will use **t****he VLOOKUP function** to repeat rows a specified number of times.

**Steps**

- In the first place, create a new column named the
**Helper Column**. - Then, we put
**1**in cell**B5**.

- After that, we will type the following formula in cell
**B6**.

`=B5+D5`

- Moreover, press
**ENTER**. - Therefore, you can see the result in cell
**B6**. - Furthermore, we will drag down the formula with the
**Fill Handle**tool.

- Therefore, you can see the complete
**Helper Column**.

**Column 2**.- Then, we enter
**1**to**15**in cells**E5**to**E20**.

- Afterward, we add a column named
**Repeat**. - Then, we type the following formula in cell
**F5**.

`=VLOOKUP(E5,$B$4:$C$10,2)`

**Formula Breakdown**

**The VLOOKUP function**looks up a certain value in an array based on specified criteria.**VLOOKUP(E5,$B$4:$C$10,2) →**becomes**Output: Laptop**

**ENTER**.- Therefore, you can see the result in cell
**F5**. - Moreover, we will drag down the formula with the
**Fill Handle**tool.

- Hence, you can see the complete
**Repeat**column.

**3. Inserting VBA Code to Repeat Rows for a Specified Number of Times in Excel**

In this method, we will insert a** VBA** code to repeat rows a specified number of times in Excel.

**Steps**

- In the first place, we will go to the
**Developer**tab >> select**Visual Basic**.

- At this point, a
**VBA Editor**window will appear. - Then, from the
**Insert**tab >> we will select**Module**.

- Then, we will type the following code in the
**Module**.

```
Sub Repeat_Data()
Dim x_range As Range
Dim in_range As Range, Outx_range As Range
xTitleId = "Repeat Rows a specified number of times"
Set in_range = Application.Selection
Set in_range = Application.InputBox(" select Range :", _
xTitleId, in_range.Address, Type:=8)
Set Outx_range = Application.InputBox("location (single cell):", _
xTitleId, Type:=8)
Set Outx_range = Outx_range.Range("A1")
For Each x_range In in_range.Rows
xValue = x_range.Range("A1").Value
xNum = x_range.Range("B1").Value
Outx_range.Resize(xNum, 1).Value = xValue
Set Outx_range = Outx_range.Offset(xNum, 0)
Next
End Sub
```

**Code Breakdown**

- Here, we declare
**Repeat_Data**as the**Sub**. - We take
**x_range**,**in_range**, and**outx_range**as**Range**. - We used the
**FOR.**..**NEXT loop**to run the code until the last value is found.

- After that, we
**Save**the code >> return to our**Worksheet**. - Now, to run the code, we go to the
**Developer**tab >> select**Macros**.

- Moreover, we select the
**Sub**>> click on**Run**.

- At this point, a prompt box appears where you have to input the range (
**$B$5:$B$10**). - In addition, click
**OK**to continue.

- Along with that, another prompt box appears >> Select a cell where you want to show your output.
- Here, we selected cell
**E5**. - In addition, click
**OK**to continue.

- As a result, we have got our specified number of repeated rows.

### 4. Combining IF, ISBLANK, INDIRECT, ROW, ROWS, and COUNTA Functions

Here, we will use the combination of **IF**, **ISBLANK**, **INDIRECT**, **ROW**, **ROWS**, and **COUNTA **functions to repeat rows a specified number of times in Excel.

**Steps**

- First of all, we put the items in the
**Item**sheet.

- Then, in the
**Repeat**sheet, we want these items to be repeated in the empty cells of the following created table.

- Furthermore, we will type the following formula in cell
**B11**.

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

** ****Formula Breakdown**

- The
**IF function**makes a logical comparison between a value we want and the given value. - The
**ISBLANK function**returnsor*TRUE*after checking on a cell reference.*FALSE* - The
**INDIRECT function**finds out the reference that is specified by the text string. - The
**ROW function**finds the row number of a cell reference. - The
**ROWS function**determines the number of rows of a range of cells. - The
**COUNTA function**counts the number of nonempty cells. **IF(ISBLANK(INDIRECT(“Item!B”&ROW(B5))),INDIRECT(“Repeat!B”&(ROWS($B$5:B5)-(COUNTA(Item!B:B)-2))),Item!B5) →**becomes**Output: Laptop**

- At this point, press
**ENTER**. - Therefore, you can see the result in cell
**B11**. - Furthermore, we will drag the formula towards the right through the
**Fill Handle**tool.

- In addition, we will drag down the formula with the
**Fill Handle**tool.

** **

**Read More:** How to Make a Pattern Repeat in Excel

### 5. Merging TRIM, TEXTJOIN, IFERROR, FILTERXML, REPT, SUBSTITUTE, and XLOOKUP Functions

In the following dataset, you can see the** Item** and **Repeat Time **columns. Here, we will use the **TRIM**, **TEXTJOIN**, **IFERROR**, **FILTERXML**, **REPT**, **ROW**, **SUBSTITUTE**, and **XLOOKUP** functions to repeat rows a specified number of times in Excel.

**Steps**

- First, we will type the following formula in cell
**E4**.

`=TRIM(TEXTJOIN(" ", TRUE, REPT(ROW(B5:B100)&" ", C5:C100)))`

**Formula Breakdown**

- The
**ROW function**finds the row number of a cell reference. - The
**REPT function**repeats the values a number of times. - The
**TEXTJOIN function**adds a number of text strings. - The
**TRIM function**trims the text string by removing extra spaces. **TRIM(TEXTJOIN(” “,TRUE,REPT(ROW(B5:B100)&” “,C5:C100)))**becomes**Output: 5 5 6 6 7 7 7 8**

**ENTER**.- Therefore, you can see the result in cell
**E4**.

- Then, we will add a
**Helper 2**column. - Furthermore, we will type the following formula in cell
**E7**.

`=IFERROR(FILTERXML( "<a><b>"&SUBSTITUTE(F4," ","</b><b>")&"</b></a>", "//b"),"")`

** **

** ****Formula Breakdown**

- The
**SUBSTITUTE function**replaces the current text with another text string. - The
**FILTERXML function**returns certaindata by using*XML***Xpath**. - The
**IFERROR function**removes any error present in the formula.

- Afterward, press
**ENTER**. - Therefore, you can see the complete
**Helper 2**column.

- Next, we add a
**Repeated Items**column. - Then, we type the following formula in cell
**D5**.

`=XLOOKUP($F$7# ,ROW(B5:$B$100 ),B5:B100)`

**Formula Breakdown**

**The ROW function**finds the row number of a cell reference.**The XLOOKUP function**looks up an item in an array.

- Next, press
**ENTER**. - Therefore, you can see the complete
**Repeated Items**column.

## Practice Section

You can download the following Excel file and practice the explained methods.

**Things to Remember**

👉 After getting the repeated rows you can easily **copy-paste** them into other places.

👉 The **VLOOKUP function** always searches for lookup values from the leftmost top column to the right. This function **Never** searches for the data on the left.

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**Conclusion**

Here, we show you** 5 **easy examples to repeat rows a specified number of times in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.

**Related Articles**

- How to Repeat Rows at Top in Excel
- How to Repeat Rows at Top of Specific Pages in Excel
- How to Repeat Rows in Excel When Printing
- [Fixed] Repeat Last Action Not Working in Excel

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

Sorry but apart from the manual do it your self method the rest fails. At least on my laptop here in Sweden.

The VBA give this error Run-time error ‘1004’:

Application-defined error

Agree with STEEN

Hello STEEN and DAV,

I have checked the methods on several laptops. They are working well here.

For your convenience, I have added a new Excel file with the VBA code.

Please download it and check if the methods are working.

Thank you. Have a good day.

Mind Blowing, I’m surprised and this formula wawo