I have a range called Labels1 that used to expand from A2 to A whatever.
Now, I have to change its functionality so that both the START and END can
change.

The old formula is this.
=OFFSET(Data!$A$2,1,0,COUNTIF(Data!$A:$A,"0"),1)
How do I change it so that $A$2 changes to a new start cell?

Thanks for your help.

Arlen

What is the new start cell? That should be your first argument in the OFFSET
formula.

Adjust the second argument to get the correct number of rows offset starting
in your start cell.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Arlen" wrote:

I have a range called Labels1 that used to expand from A2 to A whatever.
Now, I have to change its functionality so that both the START and END can
change.

The old formula is this.
=OFFSET(Data!$A$2,1,0,COUNTIF(Data!$A:$A,"0"),1)
How do I change it so that $A$2 changes to a new start cell?

Thanks for your help.

Arlen

Wigi,

After rereading my post, I see my question was very muddy. Here's the whole
scenario:

I have a finite range A2:A42. Within that range, there are 3 ranges (for
instance, A2:A14, A15:A35, A36:A42).

Depending on user input, one of those ranges will have big numbers and the
other ranges will contain Zeroes. I need a dynamic range that starts at the
first non-zero and extends to the last non-zero. So Labels1 could either be
A2:A14, A15:A35 or A36:A42.

How do you do that?

"Wigi" wrote:

What is the new start cell? That should be your first argument in the OFFSET
formula.

Adjust the second argument to get the correct number of rows offset starting
in your start cell.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Arlen" wrote:

I have a range called Labels1 that used to expand from A2 to A whatever.
Now, I have to change its functionality so that both the START and END can
change.

The old formula is this.
=OFFSET(Data!$A$2,1,0,COUNTIF(Data!$A:$A,"0"),1)
How do I change it so that $A$2 changes to a new start cell?

Thanks for your help.

Arlen