Forum Discussion
Slow Code ... adding rows to a table
Background: I have a table of transactions which is updated daily with the purchases of the day. Once a week (payday) there are a number of transactions that get insert. These transactions are the same every week. These weekly transactions are kept in a 2nd table. The creation of new records and copying of the template records is currently a manual process. I decided to automate this process.
To Date: I recorded a macro, then modified it. In the attached example there is code that will insert rows in the transactions table 3 times each using a different technique.
The Problem: Of the 4 ways to insert the rows (3 of them are in code), the fastest, by far, is 'inserting by hand'. The problem with this example is that by cutting down the data to a manageable sample size, everything seems to run pretty quickly (sample size is about 50 records, real data is over 5K records). I'm hoping someone can offer some coding technique suggestions.
Thanks in advance.
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).
I know you already found a solution that works for you, but since this is one of the top Google results for finding a way to add multiple rows more quickly, I wanted to share the solution I created in hopes that it helps someone else!
I have been revamping my company's excel-based proposal estimator over the past few months, and I was dead-set on having a button to add X number (user-filled cell) of rows into the table instead of scrolling through 200-300 unused rows when most of our bids have 30 items of less.
I am completely self-taught for all things coding/VBA in the past year, and pretty much every suggestion I ran into on Google was that running a loop of
ActiveSheet.ListObjects("TableName").ListRows.Add AlwaysInsert:=True
was the only option to add multiple.
Which is fine, if you're just adding a few rows. But again, when we need to add rows it can be 50+ and even with ScreenUpdating and Calculation turned off it was still taking upwards of 1 second per row.
So, here's the solution I came up with!
Made a new worksheet and copied one of my table rows into it so it would have the formulas, then dragged it down to autofill to row 1000-something. And used the following code that is activated by button click:
Sub AddRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim RowVal As StringRowVal = ActiveSheet.Range("RowAdd").Value 'RowAdd is a Named cell where users enter how many rows they want added
Sheets("Copy").Rows("1:" & RowVal).EntireRow.Copy
Sheets("Destination").Range("AddAbove").Insert Shift:=xlDown 'AddAbove is a Named cell in the row BELOW the last row in the table
ActiveSheet.ListObjects("TableName").Resize ActiveSheet.ListObjects("TableName").Range.Resize(ActiveSheet.ListObjects("TableName").Range.Rows.Count + RowVal)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
I know it's visually clunky since I didn't use many variables, but it will add 200 rows in about 10 seconds whereas the loop I had before was almost a minute to add 50 rows!
(Wanted to add that I didn't know about ListObjects().Resize until I was building this solution yesterday, but it wouldn't have worked for me anyway because there's a bunch of other stuff below the Estimator table)
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
