none
How to replicate a row completely, including color, format and formulas RRS feed

  • Question

  • This should be a very common operation. My Interop-based application extracts tabular data from some source and outputs it to an Excel spreadsheet. My template contains the minimum number of rows (one). That row has all the color, formatting, formulas, etc ready. Under that row, there is a footnote which will be pushed down at run time.

    Interactively, I would use the typical commands:

     - Insert the n-1 rows between the master row and the footnote in order to fit the upcoming data. The footnote is moved down.

     - Select the n rows with the master one at the top.

     - Press Ctrl-D

    How is this done programmatically?

    TIA





    Saturday, February 3, 2018 9:13 PM

All replies

  • Hi,

    I'm not sure I can understand correctly what you want to do.
    If you are using Excel and do what you want to do in Excel, you can Record Macro.
    (1) make [Developer] menu visible
         [File] > [Option] > [Customize] > check [Developer]
         
    (2) [Developer] menu on ribbon > [Record Macro]
        
    (3) do what you want to do using mouse/keyboard, and what you did will be recorded as Macro.

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Sunday, February 4, 2018 6:46 AM
    Sunday, February 4, 2018 6:44 AM
  • Thanks for your kind reply, but my application is based on Interop and C#. Therefore, I am looking for something along these lines:

    How to copy Excel cell format and formula programmatically in C#

    Sunday, February 4, 2018 6:57 AM
  • Hello Travis Banger,

    Please try to refer to below link.

                //ws is the target worksheet object
                //get lastRow's Index
                int lastRowIndex = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                int n = 10;
                //if the rows count less than 2, do nothing
                if (lastRowIndex >= 2) {
                    //insert n rows between last row and (last-1) row
                    ws.Rows[lastRowIndex+":"+ (lastRowIndex+n-1)].Insert(
                        Excel.XlInsertShiftDirection.xlShiftDown, 
                        Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                    //Ctrl+D
                    ws.Rows[(lastRowIndex - 1) + ":" + (lastRowIndex +n-1)].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.

    Monday, February 5, 2018 6:47 AM