I am using Office 2003. I have data in Column A. However, I would like to
insert x number of rows inbetween each row that has data. The data is
currently in consecutive rows. For example, if I choose to insert three
rows, then three rows should be inserted below each row that has data.
Thereafter, if I need to insert another x number of rows, then these should
be added below the three rows that were already added. This should apply to
all the rows that have data. Please assist.

Hi,

Here is a macro that I wrote several years ago. It inserts x rows between
each row within the selected range:

Public Sub Insert_Rows_betwn_existing()
Dim R As Long
Dim n As Long
Dim Rng As Range
Dim myCell As Range
Dim NumRows As Integer

If Selection.Rows.Count 1 Then
NumRows = InputBox("Enter number of rows to insert between each row
in the selection")
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set Rng = Selection
n = 0
For R = Rng.Rows.Count To 1 Step -1
Rng.Rows(R + 1).Resize(NumRows).EntireRow.Insert
' MsgBox ("row " & N)
n = n + 1
Next R
MsgBox (n & " groups of " & NumRows & " rows inserted")
Rng.Rows(R + 1).Select
Else
MsgBox ("Must select one or more rows before executing command")
End If
EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

No doubt there is a better/cleverer way to do it.

Sean

"technotronic" wrote in message
...
I am using Office 2003. I have data in Column A. However, I would like to
insert x number of rows inbetween each row that has data. The data is
currently in consecutive rows. For example, if I choose to insert three
rows, then three rows should be inserted below each row that has data.
Thereafter, if I need to insert another x number of rows, then these
should
be added below the three rows that were already added. This should apply
to
all the rows that have data. Please assist.

Sean correctly picked up that you wanted to specify then number of rows
between, so I will suggest changes to his macro, along with my preference
to not add blank rows if they are already there. (my preference).

-- cell in column A is checked to see if it has content or not
-- the cancel button on inputbox will actually cancel
-- suggested default number of rows requires change to appication.inputbox
-- only insert rows up to the quota request, be able to rerun without inserting
any blank rows since quotas have already been met. (per my preference)
-- change the msgbox, to reflect changes to the macro per my preference.
-- show the selection range originally requested including inserted rows
-- you can actually select the entire sheet now without performance penalty by
restricting to the intersection of the selected rows and the usedrange
-- rows may be inserted below the requested range to fulfill quota witin
the selection range (last row within range with content in col A)
-- lines have been shortened with continuation to help with posting of code.


Public Sub Insert_Rows_betwn_existing()
'Sean Bartleet, excel.programming, 2005-10-20
Dim R As Long
Dim n As Long
Dim Rng As Range
Dim myCell As Range
Dim NumRows As Long, J As Long, inserts As Long

If Selection.Rows.Count 1 Then
On Error Resume Next
Set Rng = Intersect(Selection, ActiveSheet.UsedRange)
If Rng.Rows.Count = 0 Then
MsgBox "selection outside of used range"
Exit Sub
End If
NumRows = Application.InputBox("Enter number of rows to insert " _
& "between each row in the selection", _
"Input number of guaranteed blank rows", 1, , , , , 1)
If NumRows = 0 Then
MsgBox "Cancelled by your command"
Exit Sub
End If
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

n = 0
For R = Rng.Rows.Count To 1 Step -1
If Rng.Cells(R, 1) < "" Then
For J = 1 To NumRows
If Rng.Cells(R + J, 1) < "" Then
Rng.Rows(R + 1).Resize(NumRows + 1 - J).EntireRow.Insert
n = n + 1
inserts = inserts + NumRows + 1 - J
End If
Next J
End If
Next R
MsgBox (n & " insertion points for" & NumRows & _
" blank rows required between populate rows, " _
& inserts & " blank rows actually inserted" _
& "within preselected range")
Rng.Select '-- show scope based on original range
Else
MsgBox ("Must select one or more rows for range " _
& "before executing command")
End If
EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"technotronic" wrote in message ...
I am using Office 2003. I have data in Column A. However, I would like to
insert x number of rows inbetween each row that has data. The data is
currently in consecutive rows. For example, if I choose to insert three
rows, then three rows should be inserted below each row that has data.
Thereafter, if I need to insert another x number of rows, then these should
be added below the three rows that were already added. This should apply to
all the rows that have data. Please assist.