I'd like to be able to get an array of values for just the visible cells on a worksheet. However, the `Range.Value2`

property only seems to work with contiguous cells.

What would be an efficient method of getting the visible cell values into a multiple dimensional array? Perhaps we could do some sort of looping over the `Range.Areas`

?

If you look at the sample code below, you will see that the `.Value2`

property only returns the values for the first `Area`

in the `Range`

.

Sample output is (you'll see that the visible values is missing the last two rows):

```
All Values:
String1 String2 String3 String4 String5
String6 String7 String8 String9 String10
String11 String12 String13 String14 String15
String16 String17 String18 String19 String20
String21 String22 String23 String24 String25
Visible Values:
String1 String2 String3 String4
String6 String7 String8 String9
```

_

```
public static void RunTest()
{
SetData();
HideRowsAndColumns();
Excel.Application excelApp = Globals.ThisAddIn.Application;
Excel.Worksheet sht = excelApp.ActiveSheet;
var usedRange = sht.UsedRange;
var visibleUsedRange = usedRange.SpecialCells(XlCellType.xlCellTypeVisible);
var usedRangeVals = (object[,])usedRange.Value2;
var visibleUsedRangeVals = (object[,]) visibleUsedRange.Value2; //this doesn't get the full set of values because Value2 does not work with non-contiguous ranges
string s = "All Values:\n\n" + ArrayToString(usedRangeVals) + "\n\nVisible Values:\n\n" +
ArrayToString(visibleUsedRangeVals);
MessageBox.Show(s);
}
private static string ArrayToString(object[,] vals)
{
int dim1Start = vals.GetLowerBound(0); //Excel Interop will return index-1 based arrays instead of index-0 based
int dim1End = vals.GetUpperBound(0);
int dim2Start = vals.GetLowerBound(1);
int dim2End = vals.GetUpperBound(1);
var sb = new StringBuilder();
for (int i = dim1Start; i <= dim1End; i++)
{
for (int j = dim2Start; j <= dim2End; j++)
{
sb.Append(vals[i, j]);
if (j != dim2End)
sb.Append("\t");
}
sb.Append("\n");
}
return sb.ToString();
}
private static void SetData()
{
const int rows = 5;
const int cols = 5;
var a = new string[rows, cols];
for (int i = 0; i < rows; i++)
for (int j = 0; j < cols; j++)
a[i, j] = "String" + (i * cols + j + 1);
Excel.Application excelApp = Globals.ThisAddIn.Application;
Excel.Worksheet sht = excelApp.ActiveSheet;
Excel.Range rng = sht.Range[sht.Cells[1, 1], sht.Cells[rows, cols]];
rng.Value2 = a;
}
private static void HideRowsAndColumns()
{
Excel.Application excelApp = Globals.ThisAddIn.Application;
Excel.Worksheet sht = excelApp.ActiveSheet;
var row = (Excel.Range) sht.Rows[3];
row.Hidden = true;
var col = (Excel.Range) sht.Columns[5];
col.Hidden = true;
}
```