Tig,

A range with non-adjacent cells is called a multiarea range.

working with multareas ranges you have to beware of many aspects:
Rows and Columns only apply to the first area...

And (ofcourse..)
you cannot use offset or cells in the manner you are used to.

First look at areas property of the Range object in VBA help.

then you'll find that
Range("data").Areas(2).Cells(1) will yield you C1



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tig wrote :

Hi all.

I have used Offset with a Named Range before to loop through a named
range, setting the values. However when i try this with a named range
that has non-adjacent columns, I can't get it to work. Here is the
simplest scenario.

If I have a named range made up of the following cells - A1:A9 and
C1:C9, both of the following lines returns the value in A1:

Range("Data").Item(1, 1).Value
Range("Data").Cells(1, 1).Value

If I want to refer to the first row but the second column in the named
range, i would expect either of the following to work:

Range("Data").Item(1, 2).Value
Range("Data").Cells(1, 2).Value

However, both of these return me the value in cell B1 !! Why? Why
not C1? Cell B1 is not even in my named range. Why is column 2 the
column physically to the right of column 1 in the range, rather then
being the second column in my named range?

Can anyone show me how to get to the second column in my named range,
without having to hardcode the number of columns to jump over?
Obviously, i know that

Range("Data").Item(1, 3).Value

will give me the value in cell C1 but that kind of invalidates the
whole point of using a named range - I shouldn't need to know the
structure of the sheet.

Thanks in advance,
Simon Cullen
remove the .nospam