Inserting rows in Excel
Inserting rows in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Inserting rows in Excel

  1. #1
    Guest

    Default

     
    I was wondering if anyone knows how to tell excel to look at a column and insert a row in between each different value

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you be a little more specific in what you want/expect ? along with some source data ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

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

    Default

    If I understand right, this Marco Written by Barrie Davidson will do what you want
    Sub Insert_row()

    ' Written by Barrie Davidson

    Dim Number_of_rows As Long

    Dim Rowinsert As Integer

    Number_of_rows = Range("A65536").End(xlUp).Row

    Ans = InputBox("How many rows do you want to insert ?", "Rows", 1)

    If IsNumeric(Ans) Then

    Rowinsert = CInt(Ans)

    Else

    MsgBox "Invalid number entered", vbCritical, "Error"

    Exit Sub

    End If

    Range("A2").Select

    Do Until Selection.Row = Number_of_rows + 1

    If Selection.Value <> Selection.Offset(-1, 0).Value Then

    Selection.EntireRow.Resize(Rowinsert).Insert

    Number_of_rows = Number_of_rows + Rowinsert

    Selection.Offset(Rowinsert + 1, 0).Select

    Else

    Selection.Offset(1, 0).Select

    End If

    Loop

    End Sub


  4. #4
    Guest

    Default

    Thanks, Paul B it worked great and will save me a lot of time. Again, THANKS!!!!!

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That is great. How would I tweak this to only insert one row at a time beneath a highlighted cell?


  6. #6

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select a cell.
    Turn on the macro recorder.
    Goto Inset>Rows.
    Turn off the macro recorder.

    You now have your macro.

  7. #7
    Board Regular
    Join Date
    Oct 2002
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatically Insert an X number of rows

    Hi,
    Further to the solution given below, I have pasted the VDB code but not getting the desired results. I want to insert 2 blank after each row with data in it. e.g

    aa
    a
    a

    should look like:
    aa


    a


    a

    after the auto insert of rows. Can anyone help pls? :o

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using something like this, and then sorting by column B

    ******** ******************** ************************************************************************>
    Microsoft Excel - Libro2___Running: xl2000 : OS = Windows ME
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    aa1
    2
    a2
    3
    a3
    4
    1
    5
    2
    6
    3
    7
    1
    8
    2
    9
    3
    Hoja1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    Board Regular
    Join Date
    Oct 2002
    Posts
    120
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    ThanQ Juan, simple but effective, it works.

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