none
What is the equivalent of these lines of VB code in C# ? RRS feed

  • Question

  • This should be an extremely common operation. I just want to grow a table, inserting new rows, keeping the existing formats, formulas, etc.

    My intention is to copy a row and duplicating it, shifting the rows below. The following BASIC code performs that:

    Dim CR As Worksheet
    Set CR = Worksheets("customer report")
    CR.Rows(15).EntireRow.Copy
    CR.Rows("16:" & 13 + i).EntireRow.Insert

    TIA



    Friday, May 25, 2018 2:58 AM

Answers

  • Hello Travis Banger,

    Ctrl+D will fill down the range. So code may be.

                Excel.Worksheet CR;
                CR = xlApplication.ActiveWorkbook.Sheets["customer report"];
                CR.Rows[16].Insert(); 
                CR.Rows["15:16"].FillDown();

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, May 25, 2018 8:33 AM

All replies

  • Please allow me to rephrase my question. This is how I would do it interactively:

     - Copy row 15

     - Insert a new, empty row below it

     - Select rows 15 and 16

     - Press Ctrl-Down

    I just found a very nice and efficient approach:

    private void shiftRows(Worksheet worksheet, int fromRow, int toRow)
    {
      worksheet.Rows[String.Format("{0}:{1}", fromRow, toRow)].Insert(XlInsertShiftDirection.xlShiftDown);
    }

    However, I would prefer a way in which the Ctrl-D operation is performed programmatically.



    Friday, May 25, 2018 3:35 AM
  • Hello Travis Banger,

    Ctrl+D will fill down the range. So code may be.

                Excel.Worksheet CR;
                CR = xlApplication.ActiveWorkbook.Sheets["customer report"];
                CR.Rows[16].Insert(); 
                CR.Rows["15:16"].FillDown();

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, May 25, 2018 8:33 AM
  • Just for C# developers handling this. 

    In this message are the words BASIC and VB used. Meant by that is VBA (Visual Basic For Applications) not the with C# parity .Net VB.


    Success
    Cor

    Friday, May 25, 2018 8:49 AM
  • Hello Travis Banger,

    Ctrl+D will fill down the range. So code may be.

                Excel.Worksheet CR;
                CR = xlApplication.ActiveWorkbook.Sheets["customer report"];
                CR.Rows[16].Insert(); 
                CR.Rows["15:16"].FillDown()

    Terry: The combination of the shiftRows() function above with your FillDown() suggestion works like a charm.  Programmers should make sure, however. to add the new rows between two existent ones, in order for the renumbering of references to work.

    Thanks!

    Friday, May 25, 2018 10:38 PM