How to read the data from Non Adjacent Cells in Excel using C#

Question

I have an excel sheet, in which i have multiple cells selected, which are not adjacent to each other. When the user click on the button, we need to read all the cells data, process it and write it to some other cell.

If the cells are adjacent to each other, i was able to get the range and able to perform the operation. But if the cells are not adjacent to each other, i am not able to get the range. The Selection.Range is always giving the address of the last cell we selected.

But we need to get the addresses of all Cells, which i am not able to do it.

Please can anybody suggest me a way to handle this scenario.

Sample code:

Range objRange = (Range) Globals.ThisAddIn.Application.Selection;
                int nColCount = objRange.Columns.Count;
                int nRowCount = objRange.Rows.Count;

Vinay,

I have tried this code based on your suggestion,

 Range objRange = (Range) Globals.ThisAddIn.Application.Selection;

        foreach (Range cell in objRange)
        {
            MessageBox.Show("" + cell.Value2);
        }

But it didn't worked. Always it's giving the last selected cell. i have selected A1, A4, A13, A16 cells. But this code is returning the A16 Cell Value Only.

Answer 1

Range inherits from IEnumerable. So you can use for each iterator to enumerate via all cells. See the equivalent VBA code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim result As String
  result = ""
  Dim c As Range
  For Each c In Me.Application.Selection
    result = result & ", " & c.Text
  Next c
  Me.Cells.Item(1, 1).Value = result 
End Sub

You can always use Range.Row, Range.Column for getting the cell address.

As said in comments, use foreach synatx:

foreach(Range cell in objRange)
{
   // now access cell's properties - c.Value will give value
}

Answer 2

After trying alot, I got the answer.

Here is the working code,

Areas objAreas = (Areas)objRange.Areas;
foreach (Range area in objAreas)
{
   string CellAddress = (GetExcelColumnName(area.Column) + "" + area.Row);
   MessageBox.Show(CellAddress);
}

GetExcelColumnName is the custom function u have written to convert Column number to Column Code(like a, b,... aa, ab.. etc)