Fastest way to get an Excel Range of Rows

Question

In a VSTO C# project I want to get a range of rows from a set of row indexes.

The row indexes can be for example like "7,8,9,12,14".

Then I want the range "7:9,12,14" rows.

I now do this:

Range rng1 = sheet.get_Range("A7:A9,A12,A14", Type.Missing);
rng1  = rng1.EntireRow;

But it's a bit inefficient due to string handling in range specification.

sheet.Rows["7:9"]

works but I can't give this

sheet.Rows["7:9,12,14"] // Fails

Answer 1

I am not an expert in C# but AFAIK you have to use the EntireRow as you have done above. The string that you are looking for can be achieved from the .Address property. For example

    private void button1_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        Microsoft.Office.Interop.Excel.Range xlRange;

        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();

        xlWorkBook = xlexcel.Workbooks.Add();

        // Set Sheet 1 as the sheet you want to work with
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlRange = xlWorkSheet.get_Range("A7:A9,A12,A14", misValue);

        MessageBox.Show(xlRange.EntireRow.Address);

        xlRange = xlWorkSheet.get_Range(xlRange.EntireRow.Address, misValue);

        MessageBox.Show(xlRange.Address);
    }

So you can write the above as

    private void button1_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        Microsoft.Office.Interop.Excel.Range xlRange;

        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();

        xlWorkBook = xlexcel.Workbooks.Add();

        // Set Sheet 1 as the sheet you want to work with
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlRange = xlWorkSheet.get_Range("$7:$9,$12:$12,$14:$14", misValue);

        MessageBox.Show(xlRange.Address);
    }

See the part

    xlRange = xlWorkSheet.get_Range("$7:$9,$12:$12,$14:$14", misValue);