sorry, i thought it was mainly the COUNTA($C:$C) you were trying to avoid.

I guess i am missing something. How/where/by-who the formula 's going to be
used.
- is it used from code only?
- can user access/modify the formula
- or is it the range of items that the users can modify in some ways?

You say the list will be populated using automation from an Access DB. Will
this range change after that?
If not, you could maybe set a workbook-level Named Range:
Assuming the range starts in C3 and the variable wsh represents your sheet:
Dim rg As Range
Set wsh = ActiveSheet
Set rg = Range(wsh.Range("C3"), wsh.Range("C65536").End(xlUp))
wsh.Parent.Names.Add Name:="ItemsList", _
RefersTo:="='" & wsh.Name & "'!" & rg.Address(True, True)
From here, anywhere in the book you can use ItemsList, eg :
=COLUMNS(ItemsList)

Regards,
Sébastien

"Joe" wrote:

sebastien,

As I said, I would rather not use offsets. Users are always unpredictable.

Thanks,

Joe

"sebastienm" wrote:

If you know you have 1 blank cell in C2 and 1 filled cell in C1, you can use
(COUNTA($C:$C)-1)
regards,
sebastien

"Joe" wrote:

I am using XL 2002.

I have a list of items in a worksheet (say in cells C3:C7). In C1, I have
the columns header and C2 is blank.

I have created a name that refers to the first item's cell (C3). Let's say
that this is called r_A (the r_ stands for 'reference'). I now need to
create a dynamic range that refer to the values in the list. I don't want to
use

=OFFSET(r_A, 0, 0, COUNTA($C:$C), 1)

because I don't want to include blank cells (since I have a value in C1),
the range that this name would refer to would extend past the list by one
cell. I also do not want to hardcode an offset value. As a programmer, I
hate offset values.

The list will be populated using automation from an Access DB.

I would appreciate any ideas that you may have.

Thanks in advance,
--
Joe

VBA Automation/VB/C++/Web and DB development

Post over in worksheetfunctions.

You will probably get plenty of good suggestions over there.

They live for this kind of stuff.

--
Regards,
Tom Ogilvy


"Joe" wrote in message
...
The idea here is to have a named, dynamic range and then simple drop the
values into the worksheet. No code will be run. The dynamic range
handles
everything.


"sebastienm" wrote:

sorry, i thought it was mainly the COUNTA($C:$C) you were trying to
avoid.

I guess i am missing something. How/where/by-who the formula 's going
to be
used.
- is it used from code only?
- can user access/modify the formula
- or is it the range of items that the users can modify in some ways?

You say the list will be populated using automation from an Access DB.
Will
this range change after that?
If not, you could maybe set a workbook-level Named Range:
Assuming the range starts in C3 and the variable wsh represents your
sheet:
Dim rg As Range
Set wsh = ActiveSheet
Set rg = Range(wsh.Range("C3"), wsh.Range("C65536").End(xlUp))
wsh.Parent.Names.Add Name:="ItemsList", _
RefersTo:="='" & wsh.Name & "'!" & rg.Address(True, True)
From here, anywhere in the book you can use ItemsList, eg :
=COLUMNS(ItemsList)

Regards,
Sébastien

"Joe" wrote:

sebastien,

As I said, I would rather not use offsets. Users are always
unpredictable.

Thanks,

Joe

"sebastienm" wrote:

If you know you have 1 blank cell in C2 and 1 filled cell in C1, you
can use
(COUNTA($C:$C)-1)
regards,
sebastien

"Joe" wrote:

I am using XL 2002.

I have a list of items in a worksheet (say in cells C3:C7). In
C1, I have
the columns header and C2 is blank.

I have created a name that refers to the first item's cell (C3).
Let's say
that this is called r_A (the r_ stands for 'reference'). I now
need to
create a dynamic range that refer to the values in the list. I
don't want to
use

=OFFSET(r_A, 0, 0, COUNTA($C:$C), 1)

because I don't want to include blank cells (since I have a value
in C1),
the range that this name would refer to would extend past the list
by one
cell. I also do not want to hardcode an offset value. As a
programmer, I
hate offset values.

The list will be populated using automation from an Access DB.

I would appreciate any ideas that you may have.

Thanks in advance,
--
Joe

VBA Automation/VB/C++/Web and DB development