how to select two separate ranges into range object
 

Lets say I have

dim xlRng1 as excel.range

And I would like to set xlRng1 to two separate blocks of data from A1:A5 and
F1:F5

So I tried doing it like this:

set xlRng1 = Range("A1:A5, F1:F5")

so then xlRng1(1,1) should equal to value of A1
but why doesn't xlRng1(1,2) equal to value of F1, but instead it keeps
giving me value of B1 and how do I fix it?

i.e. I would like xlRng1 to consist of two columns A and F, not A through F

Thanks!

Serge

how to select two separate ranges into range object
 

Hi Serge,

set xlRng1 = Range("A1:A5, F1:F5")

so then xlRng1(1,1) should equal to value of A1
but why doesn't xlRng1(1,2) equal to value of F1, but instead it keeps
giving me value of B1 and how do I fix it?

Cells(1,2) is always going to give you the cell to the right of A1 in this
case. Your range xlRng1 actually refers to the correct range, but you can
access F1 differently:

Debug.Print xlRng1.Areas(2).Cells(1, 1).Value

Pretty ugly, but it works. Discontiguous ranges are difficult to deal with
if you need to know the location of each cell. To do it, you could loop
through the values like this to find out which one resides in F1:

Dim c As Range

For Each c In rng.Cells
If c.Address = "$F$1" Then
Debug.Print c.Value
End If
Next c

But I would suggest using 2 Range objects to point to those ranges to make
them easier to work with.

--
Regards,

Jake Marx
www.longhead.com


Serge wrote:
Lets say I have

dim xlRng1 as excel.range

And I would like to set xlRng1 to two separate blocks of data from
A1:A5 and F1:F5

So I tried doing it like this:

set xlRng1 = Range("A1:A5, F1:F5")

so then xlRng1(1,1) should equal to value of A1
but why doesn't xlRng1(1,2) equal to value of F1, but instead it keeps
giving me value of B1 and how do I fix it?

i.e. I would like xlRng1 to consist of two columns A and F, not A
through F

Thanks!

Serge

how to select two separate ranges into range object
 

Thank you Jake! Areas is what I was looking for.

-----Original Message-----
Hi Serge,

set xlRng1 = Range("A1:A5, F1:F5")

so then xlRng1(1,1) should equal to value of A1
but why doesn't xlRng1(1,2) equal to value of F1, but
instead it keeps
giving me value of B1 and how do I fix it?

Cells(1,2) is always going to give you the cell to the
right of A1 in this
case. Your range xlRng1 actually refers to the correct
range, but you can
access F1 differently:

Debug.Print xlRng1.Areas(2).Cells(1, 1).Value

Pretty ugly, but it works. Discontiguous ranges are
difficult to deal with
if you need to know the location of each cell. To do it,
you could loop
through the values like this to find out which one
resides in F1:

Dim c As Range

For Each c In rng.Cells
If c.Address = "$F$1" Then
Debug.Print c.Value
End If
Next c

But I would suggest using 2 Range objects to point to
those ranges to make
them easier to work with.

--
Regards,

Jake Marx
www.longhead.com


Serge wrote:
Lets say I have

dim xlRng1 as excel.range

And I would like to set xlRng1 to two separate blocks
of data from
A1:A5 and F1:F5

So I tried doing it like this:

set xlRng1 = Range("A1:A5, F1:F5")

so then xlRng1(1,1) should equal to value of A1
but why doesn't xlRng1(1,2) equal to value of F1, but
instead it keeps
giving me value of B1 and how do I fix it?

i.e. I would like xlRng1 to consist of two columns A
and F, not A
through F

Thanks!

Serge

.