How to insert copied rows

Add-in Express™ Support Service
That's what is more important than anything else

How to insert copied rows
How to copy a row multiple times using insert.  
Jason Clark


Guest


Hello, I have a range on a worksheet. I need to add rows to this range (while updating all formulas outside the range) The easiest way I can think to do this is to take the one entire row and copy it the number of times I need to add. The problem I am having is that if I use "worksheet.Range[string.Concat(lastRow, ":", lastRow)].Copy(worksheet.Range[rowsToAdd].EntireRow);" This will copy the row perfectly however all formulas below the rows are gone. for an example I have columns a,b,c rows 1,2,3 with numbers A5 is a sum(a1:13) and that is repeated for B and C. D1 has the formula a1+c1 repeated for 2 and 3.

The action would add rows 4 and 5 as numbers and also it needs to add the formulas for d4 and d5 while also moving a5 to a7 and updating the formula to be sum(a1:a5).


I have tried several differ things I will post here

the first attempt was
if (newRowCount > oldRowCount && newRowCount != 1)
{
string rowsToAdd;
rowDiff = newRowCount - oldRowCount;

rowsToAdd = String.Concat(lastRow + 1, ":", lastRow + rowDiff);
worksheet.Range[rowsToAdd].Insert(Excel.XlInsertShiftDirection.xlShiftDown);

var fillRange = worksheet.Range[String.Concat(lastRow, ":", lastRow)];
var fillRows = worksheet.Range[String.Concat(lastRow, ":", (lastRow + rowDiff))];
fillRange.AutoFill(fillRows, Excel.XlAutoFillType.xlFillCopy);
}


however that does move the formulas it does not update them so I end up with sum(a1:A3) in a7.

if (newRowCount > oldRowCount && newRowCount != 1)
{
string rowsToAdd;
rowDiff = newRowCount - oldRowCount;

rowsToAdd = String.Concat(lastRow, ":", lastRow + rowDiff);
worksheet.Range[rowsToAdd].Insert(Excel.XlInsertShiftDirection.xlShiftDown);

var fillRange = worksheet.Range[String.Concat(lastRow, ":", lastRow)];
var fillRows = worksheet.Range[String.Concat(lastRow, ":", (lastRow + rowDiff))];
fillRange.AutoFill(fillRows, Excel.XlAutoFillType.xlFillCopy);
}

this code will correctly update the formulas below but the formulas and cells in column D are lost.

Because we clear out the data of the range first I figured the correct solution would be to problematically do it the way I would in the UI. That would be along the lines of highlighting row 3 copying it. and inserting copied cells for rows 4 and 5.


However I am struggling figuring out how to do insert copied Cells in code. I have tried setting the copy origin but that does not seem to do anything. I can copy the cells just not insert copied cells.

Thank you for any help
Posted 25 Oct, 2017 23:15:51 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Jason,

Unfortunately, your description leaves room for interpretation. Could you please send us the test workbook showing the original state of cells and the desired result? You can find the support email address in {Add-in Express installation folder}\readme.txt. Please make sure your email contains a link to this topic.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Oct, 2017 09:32:44 Top