how to select two separate ranges into range object

[Serge]

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

 

[Jake Marx]

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.

 

[Serge L]

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

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


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



.