Duplicate row according to condition

[Computerised10]

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.

 

[Reggie74]

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]

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]

Try this

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]

hi

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

 

[Reggie74]

Try this ...

'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]

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]

Then just edit this section ....

For Count = 1 To (qty [B]- 1[/B])

OR this ...

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.

 

[Computerised10]

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