VBA to insert rows
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: VBA to insert rows
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    How about this:
    Code:
    Sub a1107749a()
    'https://www.mrexcel.com/forum/excel-questions/1107749-vba-insert-rows.html
    
        Dim tbl As ListObject
        Dim x As Long
       
        Application.ScreenUpdating = False
        Set tbl = ActiveSheet.ListObjects("medline")
        x = Range("A1").Value
        With tbl
        .DataBodyRange.Rows("1:" & x).Insert
        .DataBodyRange.Rows(x + 1).Copy .DataBodyRange.Rows("1:" & x)
        End With
        Application.ScreenUpdating = True
       
    End Sub


  2. #12
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,332
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    Akuni - your code fails as it does not copy the formula from row 1 to the newly inserted rows.

    And now as I retest my code, I am getting the same result. When I tested it before I posted it, it appeared to work correctly. Very weird.
    Last edited by igold; Aug 24th, 2019 at 10:37 AM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #13
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,332
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    I think perhaps the way to do this is to convert the Table to a Range, do the work, and then convert the Range back to a Table....
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  4. #14
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Hm, I tested my code again, and it worked, the formula are copied to the new rows. And I also tested your code, and it worked as well.

  5. #15
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,332
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    Have you tried it with formulas that are not absolute references...

    Every time I run it the formula from the original Row 1 autofills upwards changing the original non absolute reference...
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  6. #16
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Quote Originally Posted by igold View Post
    Have you tried it with formulas that are not absolute references...

    Every time I run it the formula from the original Row 1 autofills upwards changing the original non absolute reference...
    I don't quite understand, isn't that what should have happened?
    I mean the first row will be the same before & after the code runs.
    Wait, are you saying that now the code is working again? I mean the formula is copied to the new rows?
    Last edited by Akuini; Aug 24th, 2019 at 11:30 AM.

  7. #17
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,332
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert rows

    Say for example you have decided to add 2 rows (the value in Cell A1). The original formula in DataBodyRange(1,1) is "=R6+S6". According to Post #1 , the OP wants identical rows of data to be created.

    After the code runs the formula that now appears in DataBodyRange(1,1) is =R4+S4 and the cell right below that is =R5+S5. The original formula resides in the next cell down (Row 3 of the table, as we have added 2 new rows).

    That is not how I am interpreting the OP.

    I hope this makes sense.

    Additionally, at this point, even if this method works for the OP, I have now created a challenge for myself to make the inserted rows contain the exact formula as was the original with no reference changes.
    Last edited by igold; Aug 24th, 2019 at 11:58 AM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  8. #18
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Hm, I got different result.
    Here's an example:
    The formula in B4 is '=A4*2' & it stays the same after the macro run.

    BEFORE:
    Excel 2013 32 bit
    A
    B
    1
    2
    2
    3
    xx yy
    4
    1
    =A4*2
    5
    2
    =A5*2
    6
    3
    =A6*2
    Sheet: Sheet2

    AFTER:
    Excel 2013 32 bit
    A
    B
    1
    2
    2
    3
    xx yy
    4
    1
    =A4*2
    5
    1
    =A5*2
    6
    1
    =A6*2
    7
    2
    =A7*2
    8
    3
    =A8*2
    Sheet: Sheet2

  9. #19
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert rows

    wrong post sorry
    Last edited by mc136355; Aug 24th, 2019 at 12:22 PM. Reason: wrong post

  10. #20
    New Member
    Join Date
    Mar 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert rows

    thought i was in the wrong thread sorry

    Hi igold
    The issue with this one is that the code line

    .ListRows(1).Range.PasteSpecial xlPasteAll

    copies into the first row but moves everything across by one column

    then second row and moves everything across by two columns and so on.

    thanks for sharing your wisdom and i am really grateful for your help MC
    Last edited by mc136355; Aug 24th, 2019 at 12:31 PM. Reason: ..

Some videos you may like

User Tag List

Tags for this Thread

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
  •