Excel VSTO: Iterate cells according to their properties

Question

I’m doing an Excel Add-In in Visual C# (VSTO)...

I wanted to iterate on the cells of a selection (which may be discontinuous) that are not "Hidden" (hidden). This selection may be large.

So, if you have 4000 lines, in Excel I make the filters (for example cells with ID<=100), and after selecting the lines press the App button. (Let’s imagine selecting A1:A4000, which only has 100 visible lines...).

How do I do this with a foreach, for example, or with a .Where (Linq)?

I tried the following code:

Excel.Range selectedRange = null;

foreach (Excel.Range row in myFullRange.Rows)
{
    if (!row.Hidden)
        selectedRange = Globals.ThisAddIn.Application.Union(selectedRange, row);
}

but this does not work: it seems that selectedRange can’t be null.

PS - I’m using Microsoft Interop...

Answer 1

I found the following solution:

            // Gets the selected range
            Excel.Worksheet activeWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
            Excel.Range activeRange = Globals.ThisAddIn.Application.Selection.Cells;
            Excel.Range expandedRange = activeRange.EntireRow; // selects entire rows

            List<string> laddr = new List<string>();

            foreach (Excel.Range r in expandedRange.Rows) // this returns only the rows not hidden
                if (!r.Hidden)
                    laddr.Add(r.Address[true,true]);

            Excel.Range selectedRange = activeWorksheet.Range[string.Join(";", laddr)];

            selectedRange.Select();

That is, I first convert the range to addresses, selecting the ones I want, and then convert the string to range...