Can't delete rows

Thanks for all your help, Gord ... if I can impose on you a little further ...

I'm having trouble creating a dynamic data range for my worksheet data.

I have manually selected the rows & columns I want to define as the starting
range (A4 through Z109 - I'm ignoring the 1st 3 rows since I use them as
headers).

Then I used Insert > Name > Define to create the dynamic range (called
Used_Data_Range) and entered the following formula:

=OFFSET(DOCs!$A$1,0,0,COUNTA(DOCs!$A:$A),COUNTA(DOCs!$1:$1))

When I check the range, I only get up to Column O and down to Row 110. Even
though there is still populated columns beyond it (the rows below are empty).

What am I doing wrong?

Also ... my understanding is that once this range is set & I start to enter
data in row 110, etc., it will automatically extend the range, carrying over
all formatting, formulas & attributes from within the range providing I have
Extend Data Range Formulas & Formats checked (which I do). Is that correct?
Or do I have to do anything else?

As usual ... thanks for your help!
--
JoAnn


"Gord Dibben" wrote:

> Down from 20,037kb to 93kb
>
> You must have filled a lot of cells with formulas<g>
>
> Good to hear and thanks for the feedback.
>
> Again, apologies for the lack of instruction.
>
> I had just finished giving the insrtructions to another poster and got the tow
> of you mixed up.
>
> None too uncommon, I might add.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Thu, 28 Feb 2008 09:21:01 -0800, JoAnn <JoAnn DeleteThis @discussions.microsoft.com>
> wrote:
>
> >That did it!!! It's down to 93KB now.
> >Thank you so much for all your help!
>
>

See in-line responses.

On Fri, 29 Feb 2008 10:05:00 -0800, JoAnn <JoAnn DeleteThis @discussions.microsoft.com>
wrote:

>Thanks for all your help, Gord ... if I can impose on you a little further ...
>
>I'm having trouble creating a dynamic data range for my worksheet data.
>
>I have manually selected the rows & columns I want to define as the starting
>range (A4 through Z109 - I'm ignoring the 1st 3 rows since I use them as
>headers).

Do not pre-select any range.

The purpose of a dynamic range is to let Excel determine the used range based on
the "refers to" formula.

>
>Then I used Insert > Name > Define to create the dynamic range (called
>Used_Data_Range) and entered the following formula:
>
>=OFFSET(DOCs!$A$1,0,0,COUNTA(DOCs!$A:$A),COUNTA(DOCs!$1:$1))

Change to =OFFSET(DOCs!$A$1,3,0,COUNTA(DOCs!$A:$A),COUNTA(DOCs!$1:$1))

The Offset,3,0 means start 3 cells down from A1 and look down from there.


>
>When I check the range, I only get up to Column O and down to Row 110. Even
>though there is still populated columns beyond it (the rows below are empty)

If the COUNTA(DOCs!$1:$1)) which means count across row 1 does not go all the
way across, perhaps your headers in row 1 only go to O1

Try entering a row which extends to the last used column. Maybe $4:$4 ?

Assuming you have data in A1:Z109

F5......enter Used_Data_Range and see what gets selected.
>
>What am I doing wrong?
>
>Also ... my understanding is that once this range is set & I start to enter
>data in row 110, etc., it will automatically extend the range, carrying over
>all formatting, formulas & attributes from within the range providing I have
>Extend Data Range Formulas & Formats checked (which I do). Is that correct?
>Or do I have to do anything else?

Yes, the formatting should follow from above when you enter data in last unused
row.

Not sure where your formulas come into play however?


Gord

>
>As usual ... thanks for your help!