Inserting rows
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Inserting rows

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Can someone help me with some code that will insert 2 rows between a used range, e.g. if I have data in rows 2-100, how do i insert 2 rows between each of these rows?

    Thanks

    Matt

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's code that will do that, and it's versatile because it involves an input box, so if you change your mind regarding how many rows to insert, just enter that number in the box.

    Select the first cell in the range where you want to insert the rows, and run this macro.

    Modify for column.

    '''''''''''''''''''''''''

    Sub AddRows()
    Dim InsQuan As Integer
    On Error Resume Next
    InsQuan = InputBox("Enter number of rows to insert:", "Your Call")
    If InsQuan <= 0 Then
    MsgBox "Invalid number entered", 16, "Action cancelled."
    Exit Sub
    End If
    Application.ScreenUpdating = False
    Do Until Selection.Value = ""
    ActiveCell.Offset(1, 0).Range("A1:A" & InsQuan).Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(InsQuan, 0).Select
    Loop
    [A1].Select
    Application.ScreenUpdating = True
    End Sub

    ''''''''''''''''''''''''

    Thanks to Mark W, who previously posted this non-VBA approach (though you asked for code in your question, this way is effective as well):

    1. Number your rows (1 to 300 or whatever the last row is) in an unused column.
    2. Paste a copy of these numbers (1 to 300 or whatever) directly beneath the copied numbers -- on unused rows. Do it again, because you want to insert 2 rows.
    3. Sort (Ascending) on the column that contains the numbers, then clear the contents (the numbers) of that column.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Tom

  4. #4

    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-18 03:56, Tom Urtis wrote:
    Thanks to Mark W, who previously posted this non-VBA approach (though you asked for code in your question, this way is effective as well):

    1. Number your rows (1 to 300 or whatever the last row is) in an unused column.
    2. Paste a copy of these numbers (1 to 300 or whatever) directly beneath the copied numbers -- on unused rows. Do it again, because you want to insert 2 rows.
    3. Sort (Ascending) on the column that contains the numbers, then clear the contents (the numbers) of that column.

    Also, a macro could be recorded by doing it this way, which would have the advantage of not needing any knowledge of VBA and perhaps having a faster run time.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com