Duplicate row according to condition

Computerised10

New Member
Joined
Oct 2, 2014
Messages
20
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.
 

Some videos you may like

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
Joined
Sep 26, 2014
Messages
51
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
Joined
Oct 2, 2014
Messages
20
Product codedescription Qty
GGHUITELEPHONE g5004
YUIONTABLE3
HIOINCHAIR1

<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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Oct 2, 2014
Messages
20

ADVERTISEMENT

hi

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

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
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 codedescriptionQty
GGHUITELEPHONE g5004
YUIONTABLE3
HIOINCHAIR1

<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
Joined
Oct 2, 2014
Messages
20

ADVERTISEMENT

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
Joined
Sep 26, 2014
Messages
51
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top