Inserting/Deleting Table Rows

[Jeff H]

When I select, say 3 cells in a column, then right-click > Insert Table Rows, three rows are inserted and it's fast. The recorded macro just shows this code three times (except that it changes the number in parentheses for each row).

Code: Select all

Selection.ListObject.ListRows.Add (1)

When I use the code below to do the same thing, it is very slow. Is there a way to replicate the manual worksheet action for Insert Table Rows in VBA?

Code: Select all

Sub AddRows(NumRowsToAdd)
Dim i

ProtectOFF (ActiveSheet.Name)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = False

For i = 1 To NumRowsToAdd
    Selection.ListObject.ListRows.Add (1)
Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = True
ProtectON (ActiveSheet.Name)

End Sub

- Jeff

[HansV]

Do you really need to add rows at the top of the table?

[Jeff H]

I prefer it in general to keep new items at the top. This particular table is for entering time and activity data and after going through several activity reports it's helpful if all the new entries are at the top to allow the user to review their work.

I could add them at the bottom and sort the table if that would speed up the row insertion, but I think I'd have to add the sort after each entry because you don't know when the data enterer will take a break. (On second thought, the form has "Enter & Next" and "Enter & Close" buttons, so the list could sort on Close.) But the manual worksheet operation inserts them at the selected cell regardless.

[HansV]

Are the rows below the table empty? If so, you could try changing

Code: Select all

    Selection.ListObject.ListRows.Add (1)

to

Code: Select all

    Selection.ListObject.ListRows.Add 1, False

This is supposed to be faster.

[Jeff H]

Unfortunately they are virtually the same. I ran a ms timer and they both took 8 or 9 seconds to insert 3 rows. Out of three tests, the use of "False" was slower twice.

I then recorded myself manually inserting 3 rows by selecting A5:A7, right-clicking, then Insert Table Rows. It was noticeably faster, but when I ran the timer on the recorded macro it was the same range as both code snippets.

Not a happy outcome, but now I know I have to live with it.

Thanks for offering the suggestion.

- Jeff

[HansV]

Does your table contain formulas that are automatically extended to newly inserted rows?

[Jeff H]

It did have one, but I changed the calculation to the VBA code and took out all the existing formulas. (It wasn't easy! Tables have a long memory about what used to be there, it seems! )

The rows are only 9 cells wide, too.

[HansV]

Hmmm... I found a suggestion to convert the table to a normal range, then insert the rows, and finally convert the range to a table again. But that wreaks havoc with the formatting...

[Jeff H]

Well, I might just try that. I only recently converted my lists to tables, and I haven't written a lot of code based on tables at this point. I might be better off abandoning the table for this specific list.

We're only talking about seconds, but this is an admin input job where someone sits with a stack of Activity Reports for the month and having to wait 8 or 9 seconds between entries would be annoying at best. Most entries are one activity at a time, and they are not too bad. But this was a new request from the organization because they want certain volunteers to go into a residence and visit anyone who needs them. So they might be there for 3 hours and see 8 patients, so each activity entered will show 22 minutes per patient (with rounding errors). It's those residential entries that would be annoying.

- Jeff

[Jeff H]

Yeah man!! That's what I'm talkin' about!

I changed the code to specify a range instead of the listrows. No change in the timing.

Then I converted the table to a range and the time went from 9,000 ms to 190 ms. Like lightening.

Thanks!!
- Jeff

P.S. Now I just have to check all my code for table references, but this is a great result.