Insert a calculated number of blank rows

  • Thread starter Thread starter Darwin
  • Start date Start date
D

Darwin

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
 
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
 
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
 
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)
 
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.
 
Back
Top