0

I have a need to add a lot of new rows to an existing list object, say thousands. I noticed that the performance is really bad when performing the operation using code, but it is very fast when doing the action via right-click context menu.

When I select 4000 rows, then right-click on the list object > Insert > Table Rows Above, the 4000 rows were added instantly! So I recorded the macro of this action to see what's being called behind the scenes, and noticed that the ListObject.ListRow.Add is being called 4000 times, like this:

Selection.ListObject.ListRows.Add (2)
Selection.ListObject.ListRows.Add (3)
Selection.ListObject.ListRows.Add (4)
.
.
.
Selection.ListObject.ListRows.Add (4001)

But now, when I execute this macro which I have recorded, it takes about 10 seconds to add 4000 rows to the list object!

Why is there a difference between adding ListRows via right-click and via code?

Even in my C# code, I've disabled ScreenUpdating, disabled EnabledEvents, and set CalculationMode to Manual, but it is still slow.

One might ask, why can't I just use Range.Insert to add the 4000 rows. It's because my user might have some other data at the sides of the list object, and adding new rows to the entire worksheet might corrupt the data. I just want my operations to impact only the list object.

It would be great if someone can tell me what I'm missing to match the performance of adding new rows via right-click context menu.

Thanks!

Improve this question
2
  • If you are using Excel Interop: Yes, its slow and unreliable(crashes often), since it uses COM+. Have you tried inserting the Range with that 4k lines? Is the other data corrupted ( I only read "might", please test it)? Else you could try using some wrapper like EPPLus(xlsx, ... ) or NPOI (xls and stuff) for more performance. Commented Feb 8, 2018 at 7:24
  • @Brad sry, are you the thread starter? He himself answered his questions (which is great) 3 hours before you wrote this comment . So why did you wrote it in the first place? For the cotent: I wanted to make sure he tried, cause he was ruling some solution out because of might corrupt the data. The first part was an advice based on experience i collected working with interop. someone just needs to select a cell in edit mode in an open excel application ... and all interop stops working until you leave the cell (also not works with an open savefile dialog, ...) Commented Feb 14, 2018 at 6:34

2 Answers 2

Reset to default
2

Shortly after posting this question, I found this solution:

var range = worksheet.Range[worksheet.Cells[startRow, firstColumn], worksheet.Cells[startRow + 4000 - 1, lastColumn]];
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

If I'm adding new rows in the middle of the list object, the startRow needs to be in the list object.

The trick here is to define a range of the number of rows you want to add, with the number of columns the list object has. Then perform an Insert operation on that range. This will only add new rows to the list object and not to the entire worksheet. If the number of columns is wrong, an exception will be thrown.

For example, I want to add 4000 rows to the list object, starting from row 10 of the worksheet. My list object has 5 columns. So, the code will be:

var range = worksheet.Range[worksheet.Cells[10, 1], worksheet.Cells[10 + 4000 - 1, 5]];
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

I've tested that this code also works when adding new rows below the list object as well.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

you could use the resize method

in VBA (assuming the table is named after "Table1"):

With ActiveSheet.ListObjects("Table1")
    .Resize Range("Table1[#All]").Resize(.Range.Rows.Count + 4000, .Range.Columns.Count)
End With

or

ActiveSheet.ListObjects("Table1").Resize Range("Table1[#All]").Resize(ActiveSheet.ListObjects("Table1").Range.Rows.Count + 4000, ActiveSheet.ListObjects("Table1").Range.Columns.Count)

I guess in C# it'll look like (change "Table1" to your actual table name):

Selection.ListObject.Resize Range("Table1[#All]").Resize(Selection.ListObject.Range.Rows.Count + 4000, Selection.ListObject.Range.Columns.Count)
Improve this answer

4 Comments

@DhinneshJeevan, any feedback?
Sorry for the late response, I actually found a suitable solution for me. Resize will work only if there is no data under the list object, and only if I want to add new rows under the list object.
Don’t understand how the first “if” in your comment should affect the solution while as for the second “if” your question states “I have a need to add a lot of new rows to an existing list object”. Finally if you have another solution you are invited to post it here and mark as the solution and have other user benefit from it.
Sorry, I was editing my comment halfway then had to attend to something. So, my requirement is that I need to be able to add thousands of new rows in the list object. It could be in between rows, or at the end of the list object. If there is some data below the list object, and you resize the list object, the data will then be part of the list object instead of being pushed down. This is not intended. I will post my solution shortly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.