VBA to insert rows

mc136355

New Member
Joined
Mar 20, 2018
Messages
36
Hi ive searched google to try and find answer to my problem but there are similar questions but dont know how to manipulate the vba to suit me.

My problem is i need to achieve the following
i enter a value into A1 e.g 10 and it would then goto the start of my table data say A16 and then copy the row of data 10 times creating 10 identical rows of data. Any help on this would be appreciated.

thanks MC
 
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.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
Hi Akuini both codes produce the same result which is what i need formulas are copied as expected. Thanks MC
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top