I set up myRange to contigious cells - now how do I get individual range
objects from it?
Ex: I set myRange = Worksheets("Sheet1").Range("B15:B17") - now I want to
get B15, B16; B17. Is there any way to get this using Excel OM?

Thanks for any suggestion!

dim myrange as range, cell as range

set myRange = Worksheets("Sheet1").Range("B15:B17")
for each cell in MyRange
msgbox cell.Address
Next

'to refer to the 2nd cell in the range
msgbox myRange(2)

--
Regards,
Tom Ogilvy


"Dev" wrote:

I set up myRange to contigious cells - now how do I get individual range
objects from it?
Ex: I set myRange = Worksheets("Sheet1").Range("B15:B17") - now I want to
get B15, B16; B17. Is there any way to get this using Excel OM?

Thanks for any suggestion!

Dim myRange as Range, cell as Range
myRange = Worksheets("Sheet1").Range("B15:B17")

for each cell in myRange
msgbox cell.Address
Next

for i = 1 to myrange.count
msgbox myrange(i).Address
Next

--
Regards,
Tom Ogilvy



"Dev" wrote:

I set up myRange to contigious cells - now how do I get individual range
objects from it?
Ex: I set myRange = Worksheets("Sheet1").Range("B15:B17") - now I want to
get B15, B16; B17. Is there any way to get this using Excel OM?

Thanks for any suggestion!

I tried the same thing from C# - I am getting "Member not found" exception.

foreach (Excel1.Range range in r)
{
string cellAddress = ((Excel1.Range)range.Cells).get_Address
(Type.Missing, Type.Missing, Excel1.XlReferenceStyle.xlA1,Type.Missing,
Type.Missing);
h.Add(cellAddress);
}

"Tom Ogilvy" wrote:

dim myrange as range, cell as range

set myRange = Worksheets("Sheet1").Range("B15:B17")
for each cell in MyRange
msgbox cell.Address
Next

'to refer to the 2nd cell in the range
msgbox myRange(2)

--
Regards,
Tom Ogilvy


"Dev" wrote:

I set up myRange to contigious cells - now how do I get individual range
objects from it?
Ex: I set myRange = Worksheets("Sheet1").Range("B15:B17") - now I want to
get B15, B16; B17. Is there any way to get this using Excel OM?

Thanks for any suggestion!

Dev wrote:
I set up myRange to contigious cells - now how do I get individual range
objects from it?
Ex: I set myRange = Worksheets("Sheet1").Range("B15:B17") - now I want to
get B15, B16; B17. Is there any way to get this using Excel OM?

Thanks for any suggestion!



To expand on Tom Ogilvy's response, you can refer to any single cell in
a contiguous range of cells with single indexing or double indexing.
Considering first a multi-row, multi-column range, myRange(1) will refer
to the upper leftmost cell; myRange(2) will refer to the next cell to
the right and so on until the rightmost column has been referred to,
then to the leftmost column in the second row. The contiguous range that
is accessible this way extends in fact to the bottom of the worksheet.
E.g., considering the range C4:E6, the references will be as follows:

myRange(1)-C4
myRange(2)-D4
myRange(3)-E4
myRange(4)-C5
myRange(5)-D5
myRange(6)-E5
myRange(7)-C6
myRange(8)-D6
myRange(9)-E6
myRange(10)-C7
myRange(11)-D7
myRange(12)-E7 and so on down to
myRange(196597)-C65536
myRange(196598)-D65536
myRange(196599)-E65536

It follows that if the range is a single row, e.g., C4:E4, the
references will be to the same cells as for C4:E6 (because the counting
goes left to right then down and back to the left and so on); and if the
range is a single column, e.g., C4:C6, the references will be

myRange(1)-C4
myRange(2)-C5
myRange(3)-C6
myRange(4)-C7 and so on down to
myRange(65533)-C65536

You can also refer to any cell in a contiguous range with 2 index
numbers as with a VBA array, and again the accessible range continues on
down the worksheet. E.g., again considering myRange=C4:E6

myRange(1,1) refers to C4
myRange(1,2) to D4
myRange(1,3) to E4
myRange(2,1) to C5
myRange(2,2) to D5
myRange(2,3) to E5
myRange(3,1) to C6
myRange(3,2) to D6
myRange(3,3) to E6
myRange(4,1) to C7
myRange(4,2) to D7
myRange(4,3) to E7 and so on down the sheet

The cells of a single row could be referred to the same way, and to a
single column, e.g., C4:C6,

myRange(1,1)-C4
myRange(2,1)-C5
myRange(3,1)-C6
myRange(4,1)-C7 and so on down the sheet.

Alan Beban