Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting and copying Rows based on a variable
I have a very long spreadsheet that has a beginning year in column A and the
ending year in column B. Column C (which currently has no data in it) = Specific Year. The rest of the row is specific information that applies to each year in the range. I need to insert the number of rows based on the number of years in the range, i.e Column A = 2004, Column B=2006. I would need to insert 3 rows and add each specific year in the range to column C. Assuming the data starts in Row 2 and Column A = 2004, Column B = 2006, Column C = " ", Column D thru Column AY has data. The resulting data should be: Row 3: Column A = " ", Column B = " ", Column C = 2004, Column D thru AY = Row 2 data Column D thru AY. Row 4 would be the same as row 3 except Column C would index to 2005. I appreciate anyone out there can help me out with a Macro. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting and copying Rows based on a variable
Hi Darwin
Try and feedback the below macro which works on the activesheet. Sub MyMacro() Dim lngRow As Long, intTemp As Integer lngRow = 2 Do If Range("A" & lngRow) < "" Then For intTemp = Range("A" & lngRow) To Range("B" & lngRow) Rows(lngRow + 1).Insert: Range("C" & lngRow + 1) = intTemp Range("D" & lngRow & ":AY" & lngRow).Copy _ Range("D" & lngRow + 1 & ":AY" & lngRow + 1) lngRow = lngRow + 1 Next End If lngRow = lngRow + 1 Loop Until lngRow Cells(Rows.Count, "A").End(xlUp).Row End Sub If this post helps click Yes --------------- Jacob Skaria "Darwin" wrote: I have a very long spreadsheet that has a beginning year in column A and the ending year in column B. Column C (which currently has no data in it) = Specific Year. The rest of the row is specific information that applies to each year in the range. I need to insert the number of rows based on the number of years in the range, i.e Column A = 2004, Column B=2006. I would need to insert 3 rows and add each specific year in the range to column C. Assuming the data starts in Row 2 and Column A = 2004, Column B = 2006, Column C = " ", Column D thru Column AY has data. The resulting data should be: Row 3: Column A = " ", Column B = " ", Column C = 2004, Column D thru AY = Row 2 data Column D thru AY. Row 4 would be the same as row 3 except Column C would index to 2005. I appreciate anyone out there can help me out with a Macro. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting and copying Rows based on a variable
Jacob
The Macro worked like a dream. It saved me many hours of clicking and inserting. I appreciate the help. Darwin "Jacob Skaria" wrote: Hi Darwin Try and feedback the below macro which works on the activesheet. Sub MyMacro() Dim lngRow As Long, intTemp As Integer lngRow = 2 Do If Range("A" & lngRow) < "" Then For intTemp = Range("A" & lngRow) To Range("B" & lngRow) Rows(lngRow + 1).Insert: Range("C" & lngRow + 1) = intTemp Range("D" & lngRow & ":AY" & lngRow).Copy _ Range("D" & lngRow + 1 & ":AY" & lngRow + 1) lngRow = lngRow + 1 Next End If lngRow = lngRow + 1 Loop Until lngRow Cells(Rows.Count, "A").End(xlUp).Row End Sub If this post helps click Yes --------------- Jacob Skaria "Darwin" wrote: I have a very long spreadsheet that has a beginning year in column A and the ending year in column B. Column C (which currently has no data in it) = Specific Year. The rest of the row is specific information that applies to each year in the range. I need to insert the number of rows based on the number of years in the range, i.e Column A = 2004, Column B=2006. I would need to insert 3 rows and add each specific year in the range to column C. Assuming the data starts in Row 2 and Column A = 2004, Column B = 2006, Column C = " ", Column D thru Column AY has data. The resulting data should be: Row 3: Column A = " ", Column B = " ", Column C = 2004, Column D thru AY = Row 2 data Column D thru AY. Row 4 would be the same as row 3 except Column C would index to 2005. I appreciate anyone out there can help me out with a Macro. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying & Inserting Rows w/o Affecting other Rows Etc. | Excel Worksheet Functions | |||
copying formulas when inserting new rows | Excel Discussion (Misc queries) | |||
need help please inserting multiple rows based on cell value then copying to data sheet | Excel Worksheet Functions | |||
Inserting rows at variable points | Excel Programming | |||
Inserting Variable Number of Rows | Excel Programming |