Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a calculated number of blank rows
I have a range of years in 2 columns. The beginning year is in column H and
the end year is in column I. In column J I have the results of subtracting Column H from Column I. I need to insert the same number of rows as this result after each entry. I would appreciate any help as this is a fairly long spreadsheet and it would be very time consuming to do it manually. Thanks Darwin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a calculated number of blank rows
Dim rw As Long
For rw = Range("I2").End(xlDown).Row To 1 Step -1 Rows(rw).Offset(1).Resize(Cells(rw, "I")).Insert Next "Darwin" wrote: I have a range of years in 2 columns. The beginning year is in column H and the end year is in column I. In column J I have the results of subtracting Column H from Column I. I need to insert the same number of rows as this result after each entry. I would appreciate any help as this is a fairly long spreadsheet and it would be very time consuming to do it manually. Thanks Darwin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a calculated number of blank rows
sorry, should be column J of course
Dim rw As Long For rw = Range("J2").End(xlDown).Row To 1 Step -1 Rows(rw).Offset(1).Resize(Cells(rw, "J")).Insert Next "Patrick Molloy" wrote: Dim rw As Long For rw = Range("I2").End(xlDown).Row To 1 Step -1 Rows(rw).Offset(1).Resize(Cells(rw, "I")).Insert Next "Darwin" wrote: I have a range of years in 2 columns. The beginning year is in column H and the end year is in column I. In column J I have the results of subtracting Column H from Column I. I need to insert the same number of rows as this result after each entry. I would appreciate any help as this is a fairly long spreadsheet and it would be very time consuming to do it manually. Thanks Darwin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a calculated number of blank rows
Darwin,
Try code like the following: Sub AAA() Dim TopRow As Long Dim BottomRow As Long Dim R As Range TopRow = 3 BottomRow = 5 Set R = Cells(BottomRow, "J") Do R(2, 1).EntireRow.Resize(R.Value).Insert If R.Row = 1 Then Exit Do End If Set R = R(0, 1) Loop Until R.Row < TopRow End Sub Change TopRow to the first row of the data. Change BottomRow to the last row of the data. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 24 Sep 2009 07:06:01 -0700, Darwin wrote: I have a range of years in 2 columns. The beginning year is in column H and the end year is in column I. In column J I have the results of subtracting Column H from Column I. I need to insert the same number of rows as this result after each entry. I would appreciate any help as this is a fairly long spreadsheet and it would be very time consuming to do it manually. Thanks Darwin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a calculated number of blank rows
Patrick
I get an application defined or object defined error with this Macro. "Patrick Molloy" wrote: sorry, should be column J of course Dim rw As Long For rw = Range("J2").End(xlDown).Row To 1 Step -1 Rows(rw).Offset(1).Resize(Cells(rw, "J")).Insert Next "Patrick Molloy" wrote: Dim rw As Long For rw = Range("I2").End(xlDown).Row To 1 Step -1 Rows(rw).Offset(1).Resize(Cells(rw, "I")).Insert Next "Darwin" wrote: I have a range of years in 2 columns. The beginning year is in column H and the end year is in column I. In column J I have the results of subtracting Column H from Column I. I need to insert the same number of rows as this result after each entry. I would appreciate any help as this is a fairly long spreadsheet and it would be very time consuming to do it manually. Thanks Darwin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a calculated number of blank rows
Chip
I copy and pasted the Macro and changed the TopRow and BottomRow to 1 and 25 respectively. I got and error code 400 when I ran the Macro. "Chip Pearson" wrote: Darwin, Try code like the following: Sub AAA() Dim TopRow As Long Dim BottomRow As Long Dim R As Range TopRow = 3 BottomRow = 5 Set R = Cells(BottomRow, "J") Do R(2, 1).EntireRow.Resize(R.Value).Insert If R.Row = 1 Then Exit Do End If Set R = R(0, 1) Loop Until R.Row < TopRow End Sub Change TopRow to the first row of the data. Change BottomRow to the last row of the data. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 24 Sep 2009 07:06:01 -0700, Darwin wrote: I have a range of years in 2 columns. The beginning year is in column H and the end year is in column I. In column J I have the results of subtracting Column H from Column I. I need to insert the same number of rows as this result after each entry. I would appreciate any help as this is a fairly long spreadsheet and it would be very time consuming to do it manually. Thanks Darwin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert blank rows | Excel Discussion (Misc queries) | |||
Text to Rows and then Insert Blank Rows | Excel Discussion (Misc queries) | |||
Insert Blank Rows | Excel Discussion (Misc queries) | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) |