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).
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 String
RowVal = 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)