none
Excel 2010 inserting lines looses some cell borders RRS feed

  • Question

  •  

    Excel 2010 VSTO 2010 .NET 4.0 Visual Studio 2010

    When I insert several lines using the following code:

    insertRange = insertRange.EntireRow;

    insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

    Some but not all of the columns loose cell borders.


    jim...
    Monday, March 14, 2011 10:58 PM

Answers

  • Mark this as not fixed but with a less than perfect workaround.

    If I insert the lines after the next to the bottom row, they insert with all their borders and the range.fill method works properly adjusting to formulas to reflect the new line numbers. 

    The bottom line gets pushed down and it's formula is adjusted to it's new line number

    Maybe in the future, I'll be able to insert lines below the bottom line.


    jim...
    • Marked as answer by Luvs2Code Friday, March 18, 2011 2:09 PM
    Friday, March 18, 2011 2:08 PM

All replies

  • Hello Jim,

    Thanks for posting. For this issue, would you tell us how you define this insertRange? Actually, I have created an Excel workbook in a machine with Office 2010 and Visual Studio 2010 installed. In the click event handler of a button, use the code as below,

    Excel.Range range1=Globals.Sheet1.Range["A3","D5"];
    Excel.Range insertRange=range1.EntireRow;
    insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

    By running this project, it will launch Excel application. In Sheet1, I set the border of some cells in Range("A3","D5") by going to Format Cells->Border. The borders do not change by executing the code above.

    To reproduce this issue on my side, would you please sharing the simple project/steps with us? 

    If there is any difference between us, just feel free to follow up. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 15, 2011 8:08 AM
  • Here is a code snippet:

          //
          rangeEnd      = quoteWrkSht.get_Range(("A" + origLastRow.ToString()),Type.Missing);
          rangeEnd.Activate();
          // Insert the required number of rows.


          insertRange   = rangeEnd.Offset[0, 0];       

          // Add the number of rows
          try
          {
            for (int inx = 0; inx < nbrRows; inx++)
            {
               insertRange = insertRange.EntireRow;
               insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            }

    If I change the statement insertRange = rangeEnd.Offset[0,0]; to .Offset[-1,0]; it inserts the nbrRows above the last row and all the cells have their proper borders.

    If I leave the statement as it is, .Offset[0,0], if am inserting at the last row and two cells, F & G out of A through H do not have a top and bottom borders.

     


    jim...
    Tuesday, March 15, 2011 2:19 PM
  • Hello again Jim,

    Actually, by using the "Offset[0, 0]" in my side, I am not able to see this issue. The code I used as below,

    Excel.Range range1=Globals.Sheet1.Range["A3","D5"];
    Excel.Range insertRange=range1.Offset[0,0];
    insertRange=range1.EntireRow;
    insertRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

    So I recommend you could do more test on your side to troubleshoot this issue: simplify the reproducing steps or code like me, and see if this issue also happens.

    If you have any concern on this post, just feel free to follow up. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 16, 2011 8:13 AM
  • It is worse than that. There is a "backing" worksheet to the front worksheet. In the same static method, the code inserts the same lines after the same row in the "backing" worksheet and it works fine.

    In the front worksheet, I've checked the borders in the two columns that change the borders in the two columns. I've even removed the borders in the last row of those two columns and reinserted them.

    I added a background color to those two columns and one adjacent column and it gets replicated down.

    Again, if I insert the rows above the last row, none of this happens.


    jim...
    Wednesday, March 16, 2011 12:34 PM
  • Mark this as not fixed but with a less than perfect workaround.

    If I insert the lines after the next to the bottom row, they insert with all their borders and the range.fill method works properly adjusting to formulas to reflect the new line numbers. 

    The bottom line gets pushed down and it's formula is adjusted to it's new line number

    Maybe in the future, I'll be able to insert lines below the bottom line.


    jim...
    • Marked as answer by Luvs2Code Friday, March 18, 2011 2:09 PM
    Friday, March 18, 2011 2:08 PM