| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting rows based on cell entries | Excel Worksheet Functions | |||
| Add Rows based on Cell contents | Excel Discussion (Misc queries) | |||
| Cell color based upon cell value | Excel Discussion (Misc queries) | |||
| generate multiple rows based on cell value | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
