Excel: getting the selected rows back

Question

I'm using Microsoft.Office.Interop.Excel, and I can't find a way to return the selected rows. What I mean by "selected" is, the row numbers themselves when you click on the row gutter on the left and select one or more contiguous or non-contiguous rows (which highlights the whole row.) This is different from selecting a region or area on the sheet itself.

So far, I've looked at app.Selection, app.Cells, app.Rows, and none of these appear to give me the rows. app.Selection gives me the actual cell content, which is NOT what I want.

Any ideas? Thanks!

Answer 1

To do this, you need to enumerate each Area in the Range, and then enumerate the Rows within each area. Somewhat confusingly, the Excel.Range.Rows property returns a collection of ranges (each representing a row), while the Excel.Range.Row property returns the row number of the top-left cell in the range.

Therefore, to get the row numbers, you should enumerate the Areas and Rows collections and then access the Range.Row. For example:

Excel.Range range = (Excel.Range)excelApp.Selection;

List<int> rowNumbers = new List<int>();

// Enumerate the Rows within each Area of the Range.
foreach (Excel.Range area in range.Areas)
{
    foreach (Excel.Range row in area.Rows)
    {
        rowNumbers.Add(row.Row);
    }
}

// Report the results.
foreach (int rowNumber in rowNumbers)
{
    MessageBox.Show(rowNumber.ToString());
}

You could even use Linq if you wanted.

Using Query Syntax:

IEnumerable<int> rowNumbers =
    from area in range.Areas.Cast<Excel.Range>()
    from row in area.Rows.Cast<Excel.Range>()
    select row.Row;

Using Method Syntax:

IEnumerable<int> rowNumbers =
    range.Areas.Cast<Excel.Range>()
    .SelectMany(area => area.Rows.Cast<Excel.Range>()
                        .Select(row => row.Row));

Update: How to Return Distinct Row Numbers:

To return distinct row numbers (which can occur when a multi-area range is selected where the areas do not comprise entire rows).

(1) The easiest is to use Linq and make use of the Distinct operator. For example:

Using Query Syntax:

IEnumerable<int> distinctRowNumbers =
    (from area in range.Areas.Cast<Excel.Range>()
     from row in area.Rows.Cast<Excel.Range>()
     select row.Row)
    .Distinct();

Using Method Syntax:

IEnumerable<int> distinctRowNumbers =
    range.Areas.Cast<Excel.Range>()
    .SelectMany(area => area.Rows.Cast<Excel.Range>()
                        .Select(row => row.Row))
    .Distinct();

(2) If not making use of Linq, then you could use a HashSet. For example:

Excel.Range range = (Excel.Range)excelApp.Selection;

HashSet<int> distinctRowNumbers = new HashSet<int>();

// Enumerate the Rows within each Area of the Range.
foreach (Excel.Range area in range.Areas)
{
    foreach (Excel.Range row in area.Rows)
    {
        distinctRowNumbers.Add(row.Row);
    }
}

// Report the results.
foreach (int rowNumber in distinctRowNumbers)
{
    MessageBox.Show(rowNumber.ToString());
}

(3) Perhaps the most intuitive approach is to convert your original range to entire rows first, and then iterate the rows. In this case, the rows are already guaranteed to be unique, so we don't need to use a HashSet or the Distinct operator.

In these examples, note the use Excel.Range.EntireRow, which is applied to the original range before enumerating the areas and rows:

Enumerating without using Linq:

List<int> distinctRowNumbers = new List<int>();

foreach (Excel.Range area in range.EntireRow.Areas)
{
    foreach (Excel.Range row in area.Rows)
    {
        distinctRowNumbers.Add(row.Row);
    }
}

Linq using query syntax:

IEnumerable<int> distinctRowNumbers =
    from area in range.EntireRow.Areas.Cast<Excel.Range>()
    from row in area.Rows.Cast<Excel.Range>()
    select row.Row;

Linq using method syntax:

IEnumerable<int> distinctRowNumbers =
    range.EntireRow.Areas.Cast<Excel.Range>()
    .SelectMany(area => area.Rows.Cast<Excel.Range>()
                        .Select(row => row.Row));

Note that the Range.EntireRow May Return Incorrect Result, but, if I understand this correctly, this only applies to Excel '95 and below, which is completely obsolete. (I would not worry about any version of Excel below Excel '97.) If you are concerned about any versioning issues, however, and do not have the luxury of testing in all Excel versions, then you might want to stick to using a HashSet, or use Linq along with the Distinct operator, to ensure that your row numbers are unique.

Hope this helps!

Mike