Page 4 of 4 FirstFirst ... 234
Results 31 to 34 of 34

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

  1. #31
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,070
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Quote Originally Posted by igold View Post
    That is the answer then, as my formulas were referencing cells outside of the table.

    However for my own benefit I am trying to write a solution that will have the desired result with references to non-table cells.

    It's an interesting case, let us know if you managed to get the answer.

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

    Default Re: VBA to insert rows

    As convoluted as it is, I believe this will add the rows to the table and not change relative references.

    Code:
    Sub InstTblRows()
    
    
        Dim tbl As ListObject
        Dim ct As Long, c As Long, trws As Long, r As Long
        Dim temtab1, temtab2
        Dim rng As Range, P As Range
        Dim fc As String
        
        Set tbl = ActiveSheet.ListObjects("medline")
        fc = tbl.DataBodyRange(1, 1).Address(0, 0)
        ct = ActiveSheet.Range("A1").Value + 1
        trws = tbl.ListRows.Count
        Set P = tbl.DataBodyRange
        temtab1 = P.Formula
        ReDim temtab2(1 To ct + trws, 1 To tbl.ListColumns.Count)
        
        For r = 1 To ct
            For c = 1 To tbl.ListColumns.Count
                temtab2(r, c) = temtab1(1, c)
            Next
        Next
        For r = ct + 1 To UBound(temtab2, 1) - 1
            For c = 1 To tbl.ListColumns.Count
                temtab2(r, c) = temtab1(r - ct + 1, c)
            Next
        Next
        
        tbl.Unlist
        Range(fc).Offset(-1, 0).Resize(UBound(temtab2, 1), UBound(temtab2, 2)).Select
        Range(fc).Resize(UBound(temtab2, 1) - 1, UBound(temtab2, 2)) = temtab2
        ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "medline"
       
    End Sub
    ​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. #33
    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

    Hi Akuini both codes produce the same result which is what i need formulas are copied as expected. Thanks MC

  4. #34
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,070
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to insert rows

    Quote Originally Posted by mc136355 View Post
    Hi Akuini both codes produce the same result which is what i need formulas are copied as expected. Thanks MC
    You're welcome, glad to help, & thanks for the feedback.
    But I think my last code is more reliable in most situation, so it's better to use that.

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
  •