Forum Discussion
Slow Code ... adding rows to a table
Thanks for the hint. I didn't end up using your suggestion, but by practicing with it I discovered something better. So thanks again, without your hint I would still be stuck.
What I found was that copying the records 'at' the bottom of the table was easy, and Excel then expands the table for me. I didn't need to do my own expansion at all.
As for adding the records at the end of the table instead of the top ... not hard to fix in this case since one of the inserted columns is a date field. I was able to record a macro of sorting the table and used it as generated (except for changing its name).
JBF_54 I expect the fastest way to extend the table is by using this:
Ldgr.Resize Ldgr.Range.Resize(Ldgr.Range.Rows.Count + Rcrds2Cpy)
Disadvantage being that the new rows are inserted at the end of the table.
Thanks for the hint. I didn't end up using your suggestion, but by practicing with it I discovered something better. So thanks again, without your hint I would still be stuck.
What I found was that copying the records 'at' the bottom of the table was easy, and Excel then expands the table for me. I didn't need to do my own expansion at all.
As for adding the records at the end of the table instead of the top ... not hard to fix in this case since one of the inserted columns is a date field. I was able to record a macro of sorting the table and used it as generated (except for changing its name).
JBF_54 To add multiple rows to the TOP of a table (ListObject) at one time, use .ListRows(1).Range.Resize(n).Insert Shift:=xlDown (where n is the number of rows to be added).
In the context of your sample workbook, the following code can be used to quickly add multiple new records to the top of the destination table:
Sub AddNewRecordsToTop() Dim tbl As ListObject, arr As Variant Set tbl = Worksheets("Ledger").ListObjects("Ledger_TBL") arr = Worksheets("Tmplts").ListObjects("Tmplt_TBL").DataBodyRange.Value tbl.ListRows(1).Range.Resize(UBound(arr, 1)).Insert Shift:=xlDown tbl.DataBodyRange(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr End Sub
