none
How to Cut a block of rows in excel sheet and paste them in another block using C#

    Question

  • Hi guys,

    I m trying to Cut a block of rows in excel sheet and paste them in another block using C#,

    Is there any help to do that?

    Thanks.

    • Moved by Roujansky_Jr Thursday, September 08, 2011 5:35 AM wrong forum (From:DirectShow Development)
    Wednesday, September 07, 2011 6:26 PM

Answers

  • Hello,

    > Is there any help to do that?

    You can record a macro in Excel while cutting and pasting the rows. Then you can translate the macro to your programming language.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Thursday, September 08, 2011 5:54 AM
  • Below is an example of using autofilter where I copied the visible rows using copy and pastespecial using the following statement

     

                 visibleRows.Copy(xlRange);

     

    when using copy I recommend only specifying the first cell of the destination range.  Excel will paste into the destination the same size area specified by the source range.

     

           static void MyAutoFilter(Excel.Application app, Excel.Workbook excelbk)
            {
                //Assume excelbk is a blank workbook
                //first setup sample dat to show how to use copy rows
                Excel.Worksheet Summarysht = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];
                Summarysht.Name = "Summary";
                Excel.Range SummmaryShtColALastCell = 
                    (Excel.Range)Summarysht.get_Range("A" + Summarysht.Rows.Count, Type.Missing);
    
                //Add header row to sheets 2 & 3 so autofiler will work
                Excel.Worksheet Sht2 = (Excel.Worksheet)excelbk.Worksheets["Sheet2"];
                Excel.Range xlRange;
                xlRange = (Excel.Range)Sht2.get_Range("A1", Type.Missing);
                xlRange.Value2 = "Header ColA";
    
                Excel.Worksheet Sht3 = (Excel.Worksheet)excelbk.Worksheets["Sheet3"];
                xlRange = (Excel.Range)Sht3.get_Range("A1", Type.Missing);
                xlRange.Value2 = "Header ColA";
    
                //write "ABC" to a cell in each worksheet which will be used as filter criteria
                xlRange = (Excel.Range)Sht2.get_Range("A5", Type.Missing);
                xlRange.Value2 = "ABC";
                xlRange = (Excel.Range)Sht3.get_Range("A5", Type.Missing);
                xlRange.Value2 = "ABC";
    
                //now demonstate how to get data
                //transverse through woprksheets
                foreach (Excel.Worksheet Sht in excelbk.Worksheets)
                {
                    //skip summary sheet
                    if (Sht.Name != "Summary")
                    {
                        //Get Last Row in workshet using Column A
                        //get last cell in column A
                        xlRange = (Excel.Range)Sht.get_Range("A" + Sht.Rows.Count, Type.Missing);
                        int LastRow = xlRange.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;
    
                        //Add autofilter to column A
                        Excel.Range xlColumnA = xlRange.EntireColumn;
                        String SearchString = "ABC";
                        int FieldNumber = 1;
                        Boolean VisibleDropDown = true;
                        xlColumnA.AutoFilter(
                            FieldNumber, SearchString, Excel.XlAutoFilterOperator.xlAnd, 
                            Type.Missing, VisibleDropDown);
    
                        //now use advance filter to get only visible rows
                        //skip header row
                        Excel.Range visibleRows = (Excel.Range)Sht.Rows["2:" + LastRow, Type.Missing];
                        visibleRows.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);
    
                        //now get last row of summary sheet to use for destination
                        LastRow = SummmaryShtColALastCell.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;
                        int NewRow = LastRow + 1;
                        xlRange = (Excel.Range)Summarysht.get_Range("A" + NewRow, Type.Missing);
    
                        visibleRows.Copy(xlRange);
    
                        Sht.AutoFilterMode = false;
    
                    }
                }
            }

     


    jdweng
    Thursday, September 08, 2011 10:48 AM

All replies

  • Hello,

    > Is there any help to do that?

    You can record a macro in Excel while cutting and pasting the rows. Then you can translate the macro to your programming language.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Thursday, September 08, 2011 5:54 AM
  • Below is an example of using autofilter where I copied the visible rows using copy and pastespecial using the following statement

     

                 visibleRows.Copy(xlRange);

     

    when using copy I recommend only specifying the first cell of the destination range.  Excel will paste into the destination the same size area specified by the source range.

     

           static void MyAutoFilter(Excel.Application app, Excel.Workbook excelbk)
            {
                //Assume excelbk is a blank workbook
                //first setup sample dat to show how to use copy rows
                Excel.Worksheet Summarysht = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];
                Summarysht.Name = "Summary";
                Excel.Range SummmaryShtColALastCell = 
                    (Excel.Range)Summarysht.get_Range("A" + Summarysht.Rows.Count, Type.Missing);
    
                //Add header row to sheets 2 & 3 so autofiler will work
                Excel.Worksheet Sht2 = (Excel.Worksheet)excelbk.Worksheets["Sheet2"];
                Excel.Range xlRange;
                xlRange = (Excel.Range)Sht2.get_Range("A1", Type.Missing);
                xlRange.Value2 = "Header ColA";
    
                Excel.Worksheet Sht3 = (Excel.Worksheet)excelbk.Worksheets["Sheet3"];
                xlRange = (Excel.Range)Sht3.get_Range("A1", Type.Missing);
                xlRange.Value2 = "Header ColA";
    
                //write "ABC" to a cell in each worksheet which will be used as filter criteria
                xlRange = (Excel.Range)Sht2.get_Range("A5", Type.Missing);
                xlRange.Value2 = "ABC";
                xlRange = (Excel.Range)Sht3.get_Range("A5", Type.Missing);
                xlRange.Value2 = "ABC";
    
                //now demonstate how to get data
                //transverse through woprksheets
                foreach (Excel.Worksheet Sht in excelbk.Worksheets)
                {
                    //skip summary sheet
                    if (Sht.Name != "Summary")
                    {
                        //Get Last Row in workshet using Column A
                        //get last cell in column A
                        xlRange = (Excel.Range)Sht.get_Range("A" + Sht.Rows.Count, Type.Missing);
                        int LastRow = xlRange.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;
    
                        //Add autofilter to column A
                        Excel.Range xlColumnA = xlRange.EntireColumn;
                        String SearchString = "ABC";
                        int FieldNumber = 1;
                        Boolean VisibleDropDown = true;
                        xlColumnA.AutoFilter(
                            FieldNumber, SearchString, Excel.XlAutoFilterOperator.xlAnd, 
                            Type.Missing, VisibleDropDown);
    
                        //now use advance filter to get only visible rows
                        //skip header row
                        Excel.Range visibleRows = (Excel.Range)Sht.Rows["2:" + LastRow, Type.Missing];
                        visibleRows.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);
    
                        //now get last row of summary sheet to use for destination
                        LastRow = SummmaryShtColALastCell.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row;
                        int NewRow = LastRow + 1;
                        xlRange = (Excel.Range)Summarysht.get_Range("A" + NewRow, Type.Missing);
    
                        visibleRows.Copy(xlRange);
    
                        Sht.AutoFilterMode = false;
    
                    }
                }
            }

     


    jdweng
    Thursday, September 08, 2011 10:48 AM
  • Thnks guys I ll try both solutions.
    Tuesday, September 13, 2011 12:56 PM