Duplicating Rows Based on Cell Value
 

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

Duplicating Rows Based on Cell Value
 

This seemed to work ok.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
'move numbers to column A
.Range("B1").EntireColumn.Cut
.Range("A1").EntireColumn.Insert

FirstRow = 1 'no headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
Next iRow

'Put 'em back
.Range("a1").EntireColumn.Cut
.Range("c1").EntireColumn.Insert

End With

End Sub


wrote:

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

--

Dave Peterson

Duplicating Rows Based on Cell Value
 

That works great, except for when the quantity is zero... Is there a
way to skip over rows that are 0?

I tried to add an if/then inside the area it does the pasting of rows,
but it doesn't like that syntax.

Suggestions?

Thanks :



Dave Peterson wrote:
This seemed to work ok.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
'move numbers to column A
.Range("B1").EntireColumn.Cut
.Range("A1").EntireColumn.Insert

FirstRow = 1 'no headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
Next iRow

'Put 'em back
.Range("a1").EntireColumn.Cut
.Range("c1").EntireColumn.Insert

End With

End Sub


wrote:

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

--

Dave Peterson

Duplicating Rows Based on Cell Value
 

The loop that does all the work could be changed like:


For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
if howmany 0 then
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
end if
Next iRow

wrote:

That works great, except for when the quantity is zero... Is there a
way to skip over rows that are 0?

I tried to add an if/then inside the area it does the pasting of rows,
but it doesn't like that syntax.

Suggestions?

Thanks :

Dave Peterson wrote:
This seemed to work ok.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
'move numbers to column A
.Range("B1").EntireColumn.Cut
.Range("A1").EntireColumn.Insert

FirstRow = 1 'no headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
Next iRow

'Put 'em back
.Range("a1").EntireColumn.Cut
.Range("c1").EntireColumn.Insert

End With

End Sub


wrote:

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

--

Dave Peterson

--

Dave Peterson

Duplicating Rows Based on Cell Value
 

Thanks Dave... I noticed that you make a lot of posts on Excel...
Why?!?! Don't you have a day job? :)






Dave Peterson wrote:
The loop that does all the work could be changed like:


For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
if howmany 0 then
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
end if
Next iRow

wrote:

That works great, except for when the quantity is zero... Is there a
way to skip over rows that are 0?

I tried to add an if/then inside the area it does the pasting of rows,
but it doesn't like that syntax.

Suggestions?

Thanks :

Dave Peterson wrote:
This seemed to work ok.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
'move numbers to column A
.Range("B1").EntireColumn.Cut
.Range("A1").EntireColumn.Insert

FirstRow = 1 'no headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
Next iRow

'Put 'em back
.Range("a1").EntireColumn.Cut
.Range("c1").EntireColumn.Insert

End With

End Sub


wrote:

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

--

Dave Peterson

--

Dave Peterson

Duplicating Rows Based on Cell Value
 

I'm retired.


wrote:

Thanks Dave... I noticed that you make a lot of posts on Excel...
Why?!?! Don't you have a day job? :)

Dave Peterson wrote:
The loop that does all the work could be changed like:


For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
if howmany 0 then
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
end if
Next iRow

wrote:

That works great, except for when the quantity is zero... Is there a
way to skip over rows that are 0?

I tried to add an if/then inside the area it does the pasting of rows,
but it doesn't like that syntax.

Suggestions?

Thanks :

Dave Peterson wrote:
This seemed to work ok.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
'move numbers to column A
.Range("B1").EntireColumn.Cut
.Range("A1").EntireColumn.Insert

FirstRow = 1 'no headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
Next iRow

'Put 'em back
.Range("a1").EntireColumn.Cut
.Range("c1").EntireColumn.Insert

End With

End Sub


wrote:

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Duplicating Rows Based on Cell Value
 

I'd say that's good news for all of us who need your expertise!
--
smither fan


"Dave Peterson" wrote:

I'm retired.


wrote:

Thanks Dave... I noticed that you make a lot of posts on Excel...
Why?!?! Don't you have a day job? :)

Dave Peterson wrote:
The loop that does all the work could be changed like:


For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
if howmany 0 then
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
end if
Next iRow

wrote:

That works great, except for when the quantity is zero... Is there a
way to skip over rows that are 0?

I tried to add an if/then inside the area it does the pasting of rows,
but it doesn't like that syntax.

Suggestions?

Thanks :

Dave Peterson wrote:
This seemed to work ok.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim HowMany As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
'move numbers to column A
.Range("B1").EntireColumn.Cut
.Range("A1").EntireColumn.Insert

FirstRow = 1 'no headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "A").Value
.Cells(iRow, "B").Resize(1, 3).Copy
NewWks.Cells(oRow, "A").Resize(HowMany, 3).PasteSpecial _
Paste:=xlPasteValues
oRow = oRow + HowMany
Next iRow

'Put 'em back
.Range("a1").EntireColumn.Cut
.Range("c1").EntireColumn.Insert

End With

End Sub


wrote:

I can't figure out to program this, please help!

I have rows of data like this:
Name | Quantity | Attribute 1 | Attribute 2
EX1 | 2 | att1 | att2
EX2 | 3 | att1 | att2
EX3 | 2 | att1 | att2

I want it to expand the quantities into rows to look like this:
Name | attribute 1 | attribute 2 | etc
EX1 | att1 | att2
EX1 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX2 | att1 | att2
EX3 | att1 | att2
EX3 | att1 | att2

The Attribute 1 column and Attribute 2 column are just columns that
hold data that needs to be copied to each row.

This is to make a mail merge with multiple labels with the same
information!

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson