Range.Value and Range.Value2 should work for non-contiguous ranges
Range.Value and Range.Value2 will return the values of only the first area in a range if the range has multiple areas.
If you are using, for example, SpecialCells to get the visible cells in a range, the range can have multiple areas and we should be able to use Range.Value and Range.Value2 to get all the visible cells values as an array instead of just getting them for the first area in the range.
Excel vba has the possibility to easilly (and fast) transfer range values to a Variant Array.
Variant Array = Range transfers, in ONE shot, all the values in the range to the array, self dimenssioning the array to the size of the data tranfered. Excellent!
BUT ... it doesn't work if the range has multiple areas (more than 1)
Seem to me it should work, and produce an 1 dim array of 2 dim arrays (each area in the range).
Of course we can do it by cycling over the range areas. But, if it worked, it would be far more efficient, and code clean.
Harlan Grove commented
Returning an array of arrays? If only VBA were APL2. Or maybe a simple Collection object with each area an Item? More reasonable.
Even so, I can't see a pressing need. Doesn't SpecialCells return a Range object with multiple areas, and can't it be iterated through using For Each?
It should be the same dimensions as if you were to copy the visible cells to the clipboard. When you do a copy on a range in Excel, only the visible cells in the range are copied and the clipboard will basically be a tab separated value string. If that string were parsed into a 2d array, then you'd have an array of the Range.Text property(not Range.Value or Value2). The other way you could do this is to loop over the areas to build the 2d array and this could be done using Value or Value2. Both of these methods are overly complicated and there should be built in support for this.
Kenneth Barber commented
I don't think that there's a definite way to treat non-contiguous ranges.
For example, you could make a 1D array out of the values, but then you run into the issue of which order the values should go in.
You could instead make a single 2D array that contains your range and has null values for cells that are not part of your range, but in that case, you could always write a function yourself that does this.
Lastly, you can loop through the areas of your range and store the values however you like. Again, you could write such a function yourself.