+ Reply to Thread
Results 1 to 3 of 3

Inserting multiple rows in excel with data in consecutive rows

  1. #1
    technotronic
    Guest

    Inserting multiple rows in excel with data in consecutive rows

    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.

  2. #2
    Sean Bartleet
    Guest

    Re: Inserting multiple rows in excel with data in consecutive rows

    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" <[email protected]> wrote in message
    news:[email protected]...
    >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.




  3. #3
    David McRitchie
    Guest

    Re: Inserting multiple rows in excel with data in consecutive rows

    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" <[email protected]> wrote in message news:[email protected]...
    > 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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1