How to get only selected cells value from filtered cells from excel

Question

I have a simple excel sheet:

Now, I filter it such that cell value > 1. Now my data looks like:

Now, I select the data that I require:

Note that I have selected all the Mobile Numbers.

Now in my code, I am trying to retrieve all the selected data as follows:

Range selection = (Range)Globals.ThisAddIn.Application.ActiveWindow.Selection;

But, it gives me the cells from starting to ending. I think excel selects the non-visible rows also. Because row no 4 that contains 0 is also retrieved. Look at the image below:

So, now I created another Range and tried to add all the values of cells that are visible as follows:

Range onlyFilteredSelection = selection.Cells.SpecialCells(XlCellType.xlCellTypeVisible);

Now, I can see that c# shows me only the two rows. Why is it not displaying the last row, which is after the non-filtered row. Take a look at the values here:

Update:

After posting this question, I got a thought in my mind that I might be getting multiple ranges instead of 1 and so, I started exploring. And look what I have found. I found that I was exactly right. I get multiple ranges.

Here is the code that I have tried:

Range selection = (Range)Globals.ThisAddIn.Application.ActiveWindow.Selection;

List<Range> onlyFilteredSelection = new List<Range>();

foreach (Range range in selection.Cells.SpecialCells(XlCellType.xlCellTypeVisible))
{
    onlyFilteredSelection.Add(range);
}

Now, I get 4 items in selection variable. And in onlyFilteredSelection has got 3 items.

Now, I am in another trouble:

Previously, I was getting a Range, so I converted it to a Comma-Separated String very much easily using the below mentioned code:

string[] AllRecepientMobileNumberValues = ((Array)(selection.Cells.Value2)).OfType<object>().Select(o => o.ToString()).ToArray();
string RecepientMobileNumberValue = AllRecepientMobileNumberValues.Aggregate((a, x) => a + ", " + x);            

But now, I get a List. So, now my big question is how to Convert a List to Comma-Separated string?????????

Answer 1

You can use one more Select to get the values out of a list.

string[] AllRecepientMobileNumberValues = onlyFilteredSelection.Select(x => x.Cells.Value2).OfType<object>().Select(o => o.ToString()).ToArray();
string RecepientMobileNumberValue = AllRecepientMobileNumberValues.Aggregate((a, x) => a + ", " + x);