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

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

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

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

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

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