Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Rows
Please forgive me for any newb mistakes.
I have an Excel document with ~300 rows of data. I have found that I need to add 5 rows to each existing row (each of these rows contains the same data). Is there a macro I can build to do this? Example: 1. Joe California Dog House 2. Kim Georgia Cat Apartment .......etc For each row (1 through 300) I need to add 5 rows (Phone, Spouse, Children, School, Degree). I can obviously Copy-- Insert copied cells, but that requires me to manually do so for ~300 rows, re-copying for each new insert (UGH). The end sheet would look like: 1. Joe California Dog House 2. Phone 3. Spouse 4. Children 5. School 6. Degree 7. Kim Georgia Cat Apartment 8. Phone 9. Spouse 10. Children 11. School 12. Degree ......etc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Rows
Larry,
Try Dim RowNdx As Long Dim Arr As Variant Arr = Application.Transpose(Array("phone", "spouse", _ "children", "school", "degree")) For RowNdx = 2 To 10 * 5 Step 6 Rows(RowNdx).Resize(5).Insert Cells(RowNdx, 1).Resize(5, 1).Value = Arr Next RowNdx Change the 10 to the last row number of your data. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "LarryO" wrote in message ... Please forgive me for any newb mistakes. I have an Excel document with ~300 rows of data. I have found that I need to add 5 rows to each existing row (each of these rows contains the same data). Is there a macro I can build to do this? Example: 1. Joe California Dog House 2. Kim Georgia Cat Apartment ......etc For each row (1 through 300) I need to add 5 rows (Phone, Spouse, Children, School, Degree). I can obviously Copy-- Insert copied cells, but that requires me to manually do so for ~300 rows, re-copying for each new insert (UGH). The end sheet would look like: 1. Joe California Dog House 2. Phone 3. Spouse 4. Children 5. School 6. Degree 7. Kim Georgia Cat Apartment 8. Phone 9. Spouse 10. Children 11. School 12. Degree .....etc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Rows
Larry,
The following is better than my previous reply: Dim RowNdx As Long Dim Arr As Variant Dim StartRow As Long Dim EndRow As Long StartRow = 1 '<<< CHANGE to appropriate row number EndRow = 10 '<<< CHANGE to appropriate row number Arr = Application.Transpose(Array("phone", "spouse", _ "children", "school", "degree")) For RowNdx = StartRow + 1 To (EndRow + 2) * 5 Step 6 Rows(RowNdx).Resize(5).Insert Cells(RowNdx, 1).Resize(5, 1).Value = Arr Next RowNdx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "LarryO" wrote in message ... Please forgive me for any newb mistakes. I have an Excel document with ~300 rows of data. I have found that I need to add 5 rows to each existing row (each of these rows contains the same data). Is there a macro I can build to do this? Example: 1. Joe California Dog House 2. Kim Georgia Cat Apartment ......etc For each row (1 through 300) I need to add 5 rows (Phone, Spouse, Children, School, Degree). I can obviously Copy-- Insert copied cells, but that requires me to manually do so for ~300 rows, re-copying for each new insert (UGH). The end sheet would look like: 1. Joe California Dog House 2. Phone 3. Spouse 4. Children 5. School 6. Degree 7. Kim Georgia Cat Apartment 8. Phone 9. Spouse 10. Children 11. School 12. Degree .....etc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Rows
That worked awesome. I failed to indicate however, that I
needed that to go into column 3 of my spreadsheet, not Column 1. Is there a quick fix to do that? I obviously have a backup :P, so can re-run it. Thank you for such a quick and helpful reply. My failure to include the proper information is the only reason it failed to be "perfect". LarryO -----Original Message----- Larry, Try Dim RowNdx As Long Dim Arr As Variant Arr = Application.Transpose(Array("phone", "spouse", _ "children", "school", "degree")) For RowNdx = 2 To 10 * 5 Step 6 Rows(RowNdx).Resize(5).Insert Cells(RowNdx, 1).Resize(5, 1).Value = Arr Next RowNdx Change the 10 to the last row number of your data. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "LarryO" wrote in message ... Please forgive me for any newb mistakes. I have an Excel document with ~300 rows of data. I have found that I need to add 5 rows to each existing row (each of these rows contains the same data). Is there a macro I can build to do this? Example: 1. Joe California Dog House 2. Kim Georgia Cat Apartment ......etc For each row (1 through 300) I need to add 5 rows (Phone, Spouse, Children, School, Degree). I can obviously Copy-- Insert copied cells, but that requires me to manually do so for ~300 rows, re-copying for each new insert (UGH). The end sheet would look like: 1. Joe California Dog House 2. Phone 3. Spouse 4. Children 5. School 6. Degree 7. Kim Georgia Cat Apartment 8. Phone 9. Spouse 10. Children 11. School 12. Degree .....etc . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Rows
Thanks, both work great. I was able to figure out that
replacing: Cells(RowNdx, 1).Resize(5, 1).Value = Arr with Cells(RowNdx, 3).Resize(5, 1).Value = Arr Did exactly what I needed. Thank you so much!! -----Original Message----- Larry, The following is better than my previous reply: Dim RowNdx As Long Dim Arr As Variant Dim StartRow As Long Dim EndRow As Long StartRow = 1 '<<< CHANGE to appropriate row number EndRow = 10 '<<< CHANGE to appropriate row number Arr = Application.Transpose(Array("phone", "spouse", _ "children", "school", "degree")) For RowNdx = StartRow + 1 To (EndRow + 2) * 5 Step 6 Rows(RowNdx).Resize(5).Insert Cells(RowNdx, 1).Resize(5, 1).Value = Arr Next RowNdx -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "LarryO" wrote in message ... Please forgive me for any newb mistakes. I have an Excel document with ~300 rows of data. I have found that I need to add 5 rows to each existing row (each of these rows contains the same data). Is there a macro I can build to do this? Example: 1. Joe California Dog House 2. Kim Georgia Cat Apartment ......etc For each row (1 through 300) I need to add 5 rows (Phone, Spouse, Children, School, Degree). I can obviously Copy-- Insert copied cells, but that requires me to manually do so for ~300 rows, re-copying for each new insert (UGH). The end sheet would look like: 1. Joe California Dog House 2. Phone 3. Spouse 4. Children 5. School 6. Degree 7. Kim Georgia Cat Apartment 8. Phone 9. Spouse 10. Children 11. School 12. Degree .....etc . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting rows inbetween rows of data ? | Excel Discussion (Misc queries) | |||
Copying & Inserting Rows w/o Affecting other Rows Etc. | Excel Worksheet Functions | |||
Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
inserting rows | Excel Discussion (Misc queries) | |||
Inserting Rows | Excel Worksheet Functions |