CODEWITHSUNDEEP
c#vbaexcel
Dhinnesh Jeevan
Dhinnesh Jeevan

Reputation: 549

C# Excel - Performance when adding a lot of rows to list object

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!

Upvotes: 0

Views: 1237

Answers (2)

Dhinnesh Jeevan
Dhinnesh Jeevan

Reputation: 549

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.

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

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)

Upvotes: 0

Related Questions