How to Repeat Rows in Excel Based on Cell Value (4 Easy Ways)

In this article, we will use four effective methods to repeat rows based on cell values in Excel: using VBA code, the VLOOKUP function, the IF function, and finally by copying and pasting the cells. We used the Microsoft Office 365 version here, but you can utilize any other version at your disposal.

Method 1 – Using VBA Code

To use VBA code, you first need to have the Developer tab showing on your ribbon. Click here to see how to show the Developer tab on your ribbon.

Steps:

To open the VBA window, go to the Developer tab on your ribbon.
Select Visual Basic from the Code group.

Embedding VBA Code to Repeat Rows in Excel Based on Cell Value

VBA modules hold the code in the Visual Basic Editor. They have a .bcf file extension. We can create or edit code easily through the VBA editor window. To insert a module for the code:

Go to the Insert tab on the VBA editor.
Click on Module from the drop-down.

insert module

As a result, a new module will be created.

Select the module if it isn’t already selected and enter the following code in it:

Sub RepeatData()
'Repeat Rows
Dim use_range As Range
Dim input_range As Range, output_range As Range
xTitleId = "Repeat Rows in Excel"
Set input_range = Application.Selection
Set input_range = Application.InputBox("Range :", xTitleId, input_range.Address, Type:=8)
Set output_range = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
Set output_range = output_range.Range("A1")
For Each use_range In input_range.Rows
            y_value = use_range.Range("A1").Value
            w_num = use_range.Range("B1").Value
    output_range.Resize(w_num, 1).Value = y_value
            Set output_range = output_range.Offset(w_num, 0)
Next
End Sub

Save the code.
Close the VBA window.
Go to the Developer tab again.
Select Macros from the Code group.
Select the Macro name you have just entered, here RepeatData.
Click on Run.

In the prompt box that appears, input the range ($B$5:$C$9).
Click OK to continue.

Select a cell to return the output ($E$5).
Click OK to continue.

We have repeated rows based on cell values.

Method 2 – Using the VLOOKUP function

We can also repeat rows in Excel based on cell values using the VLOOKUP function.

Steps:

Create three new columns named Column 1, Column 2, and Repeat Time.
In the Repeat Time column, enter the number of times the rows should be repeated.

In Column 1, we will add a formula for the VLOOKUP function to use.

In cell B6 enter the following formula:

=B5+D5

Press Enter.

Use the Fill Handle to copy the formula down to the bottom of the range.

Column 1 is filled as shown below.

Make another column and name it Column 2.
Enter 1 in cell E5 and use the Fill Handle to copy values down to 13, the sum of the values in Column 2.
Insert a new column named Repeat.
In cell F5, apply the following VLOOKUP function:

=VLOOKUP(E5,$B$5:$C$9,2)

Here, the lookup_value is E5, the lookup_array is $B$5:$E$9 and the col_Index_num is 2.

Press Enter.

Use the Fill Handle to apply the same formula to the rest of the cells.

The Rows are repeated based on cell value as shown below.

Method 3 – Using the IF Function

Now we’ll repeat rows based on cell values using the IF function.

Steps:

In cell E5 enter the following formula:

=IF(D5<$D$8,$B$5,"")

Press Enter.

Drag the Fill Handle down to fill the cells below with the formula.

Cell C5 is repeated twice in the Repeat column.

In cell E8 enter the following formula:

=IF(D8< $D$10,$B$6,"")

Press Enter.

Drag the Fill Handle down to fill the cells below with the formula.

Cell C6 is repeated once in the Repeat column.

In cell E10 enter the following formula:

=IF(D10<$D$14,$B$7,"")

Press Enter.

Drag the Fill Handle down to fill the cells below with the formula.

Cell C7 is repeated four times in the Repeat column.

In cell E14 enter the following formula:

=IF(D14<$D$16,$B$8,"")

Press Enter.

Drag the Fill Handle down to fill the cells below with the formula.

Cell C8 is repeated once in the Repeat column.

In cell E16 enter the following formula:

=IF(D16<=$D$17,$B$9,"")

Press Enter.

Drag the Fill Handle down to fill the cells below with the formula.

Cell C9 is repeated once in the Repeat column.

How Does the Formula Work?

IF(D5<$D$8,$B$5,””)

Checks whether the value of cell D5 is within cell D8 or not. If the condition is met, the function will return the value of cell B5. Otherwise, it returns a blank cell and moves to the next cell. In this way, cell B5 will be repeated in the Repeat column.

IF(D8< $D$10,$B$6,””)

Checks whether the value of cell D8 is within cell D10 or not. If the condition is met, the function will return the value in cell B6. Otherwise, it returns a blank cell and moves to the next cell. In this way, cell B6 will be repeated in the Repeat column.

IF(D10<$D$14,$B$7,””)

Checks whether the value of cell D10 is within cell D14 or not. If the condition is met, the function will return the value in cell B7. Otherwise, it returns a blank cell and moves to the next cell. In this way, cell B7 will be repeated in the Repeat column using this formula.

IF(D14<$D$16,$B$8,””)

Checks whether the value of cell D14 is within cell D16 or not. If the condition is met, the function will return the value in cell B8. Otherwise, it returns a blank cell and moves to the next cell. In this way, cell B8 will be repeated in the Repeat column using this formula.

IF(D16<=$D$17,$B$9,””)

Check whether the value of cell D16 is within cell D17 or not. If the condition is met, the function will return the value in cell B9. Otherwise, it returns a blank cell and moves to the next cell. In this way, cell B9 will be repeated in the Repeat column using this formula.