# Duplicate row according to condition

#### Computerised10

##### New Member
Hi all

I need to duplicate a row according to the value. So if the qty is 1 I don't need to duplicate but if greater than one let's say 5 I need it to duplicate the row 5 times.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Reggie74

##### Board Regular
It would be helpful if you provide sample data to show what you require ..

Hi all

I need to duplicate a row according to the value. So if the qty is 1 I don't need to duplicate but if greater than one let's say 5 I need it to duplicate the row 5 times.

#### Computerised10

##### New Member
 Product code description Qty GGHUI TELEPHONE g500 4 YUION TABLE 3 HIOIN CHAIR 1

<tbody>
</tbody>

So if qty is 4 duplicate row 4 times. If qty is one do no duplicate. Hope this make it clearer. Thanks

#### Special-K99

##### Well-known Member
Try this

Code:
``````Sub k1()
Dim LastRow As Long, i, j, k, LastCol As Integer
k = 0
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
For j = 1 To Sheets("Sheet1").Range("A1").Offset(i - 1, 2)
k = k + 1
Sheets("Sheet2").Range("A1").Offset(k - 1, 0) = Sheets("Sheet1").Range("A1").Offset(i - 1, 0)
Sheets("Sheet2").Range("A1").Offset(k - 1, 1) = Sheets("Sheet1").Range("A1").Offset(i - 1, 1)
Next j
Next i
End Sub``````

Output on Sheet2

#### Computerised10

##### New Member

hi

it returns an error on line For j = 1 To Sheets("Sheet1").Range("A1").Offset(i - 1, 2)

#### Reggie74

##### Board Regular
Try this ...

Code:
``````'this subroutine duplicates rows if the quantity is > 1
Sub duplicaterow()

Dim lastrow As Long             ' denotes the last row populated with a value in Column C - (Qty)
Dim qty As Integer              ' denotes the quantity and the no.of times to duplicate if > 1

lastrow = Range("C65536").End(xlUp).Row
Range("C2").Select

For i = 2 To lastrow
If ActiveCell.Value > 1 Then
qty = ActiveCell.Value
For Count = 1 To qty
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
Next Count
ActiveCell.Offset(1, 2).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Next i
End Sub``````

 Product code description Qty GGHUI TELEPHONE g500 4 YUION TABLE 3 HIOIN CHAIR 1

<tbody>
</tbody>

So if qty is 4 duplicate row 4 times. If qty is one do no duplicate. Hope this make it clearer. Thanks

#### Computerised10

##### New Member

thanks a million that's exactly what i needed except with the QTY if it says 7 i only want 7 lines, this leaves the original line and add another seven making it 8 lines in total.

#### Reggie74

##### Board Regular
Then just edit this section ....
Code:
``For Count = 1 To (qty [B]- 1[/B])``

OR this ...

Code:
``For Count = 0 To qty``

thanks a million that's exactly what i needed except with the QTY if it says 7 i only want 7 lines, this leaves the original line and add another seven making it 8 lines in total.

Last edited:

#### Computerised10

##### New Member
For Count = 1 To (qty - 1) worked perfect. thanks man i really appreciate your help.
</pre>

Replies
1
Views
44
Replies
13
Views
142
Replies
17
Views
169
Replies
4
Views
84
Replies
4
Views
56