Can't delete rows

Once again you've solved the problem! It works great now ... thanks for all
your help!
--
JoAnn


"Gord Dibben" wrote:

See in-line responses.

On Fri, 29 Feb 2008 10:05:00 -0800, JoAnn
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(D OCs!$1:$1))

Change to =OFFSET(DOCs!$A$1,3,0,COUNTA(DOCs!$A:$A),COUNTA(DO Cs!$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!

Good to hear.

Thanks for the feedback.

On Tue, 4 Mar 2008 07:40:00 -0800, JoAnn
wrote:

Once again you've solved the problem! It works great now ... thanks for all
your help!

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a sheet.

The reason you have such a large used range is the copying of formulas down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn
wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't done
that since I didn't know I had to and don't know how to do it. I couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations. But
when I format or add a formula to a cell/column/row, I generally copy it down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn

I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation.

Then follow Shift + End + Downarrow and EditDeleteEntire Row.


Gord

On Fri, 4 Sep 2009 11:14:01 -0700, jabe813
wrote:

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a sheet.

The reason you have such a large used range is the copying of formulas down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn
wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't done
that since I didn't know I had to and don't know how to do it. I couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations. But
when I format or add a formula to a cell/column/row, I generally copy it down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn

Are you doing it at the far left row NUMBERS
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jabe813" wrote in message
...
Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating
Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to
IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a
sheet.

The reason you have such a large used range is the copying of formulas
down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case"
you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn

wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't
done
that since I didn't know I had to and don't know how to do it. I
couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations.
But
when I format or add a formula to a cell/column/row, I generally copy it
down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn

My apologies.

I tried right-click and Edit
My option there is EditDelete Row

Can you tell me how to turn off automatic calculation?
I have already tried clearing formatting and clearing contents in these rows
& columns.

Thank you!


"Gord Dibben" wrote:

I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation.

Then follow Shift + End + Downarrow and EditDeleteEntire Row.


Gord

On Fri, 4 Sep 2009 11:14:01 -0700, jabe813
wrote:

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a sheet.

The reason you have such a large used range is the copying of formulas down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn
wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't done
that since I didn't know I had to and don't know how to do it. I couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations. But
when I format or add a formula to a cell/column/row, I generally copy it down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn

In xl2003 menus:
Tools|Option|Calculation tab

Don't forget to turn it back on to automatic when you're done (well, if that's
what you want).

jabe813 wrote:

My apologies.

I tried right-click and Edit
My option there is EditDelete Row

Can you tell me how to turn off automatic calculation?
I have already tried clearing formatting and clearing contents in these rows
& columns.

Thank you!

"Gord Dibben" wrote:

I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation.

Then follow Shift + End + Downarrow and EditDeleteEntire Row.


Gord

On Fri, 4 Sep 2009 11:14:01 -0700, jabe813
wrote:

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a sheet.

The reason you have such a large used range is the copying of formulas down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn
wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't done
that since I didn't know I had to and don't know how to do it. I couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations. But
when I format or add a formula to a cell/column/row, I generally copy it down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn





--

Dave Peterson

I have tried everything to delete these rows in order to reduce the file size.

Tried a variation of similar direction:
http://www.contextures.com/xlfaqApp.html#Unused

Any suggestions?? Anyone?

"Gord Dibben" wrote:

I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation.

Then follow Shift + End + Downarrow and EditDeleteEntire Row.


Gord

On Fri, 4 Sep 2009 11:14:01 -0700, jabe813
wrote:

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a sheet.

The reason you have such a large used range is the copying of formulas down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn
wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't done
that since I didn't know I had to and don't know how to do it. I couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations. But
when I format or add a formula to a cell/column/row, I generally copy it down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn

I repeat my offer

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jabe813" wrote in message
...
I have tried everything to delete these rows in order to reduce the file
size.

Tried a variation of similar direction:
http://www.contextures.com/xlfaqApp.html#Unused

Any suggestions?? Anyone?

"Gord Dibben" wrote:

I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation.

Then follow Shift + End + Downarrow and EditDeleteEntire Row.


Gord

On Fri, 4 Sep 2009 11:14:01 -0700, jabe813
wrote:

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating
Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to
IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on
a sheet.

The reason you have such a large used range is the copying of formulas
down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in
case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn

wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't
done
that since I didn't know I had to and don't know how to do it. I
couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for
calculations. But
when I format or add a formula to a cell/column/row, I generally copy
it down
so it exists in all worksheet rows/columns (I use the keyboard
sequence
Ctrl-Shift-DownArrow to select them then paste my formula or
formatting
change). That's when I noticed that I had a lot of rows. Could that
be
causing a problem?

Thanks for all your help with this!
JoAnn

After deleting unused rows and columns and saving, closing and reopening
your file size is still very large?

What is current file size under FilePropertiesGeneral?

You will always have 256 columns and 65536 rows so don't think that deleting
will remove those cells.


Gord

On Wed, 9 Sep 2009 08:28:05 -0700, jabe813
wrote:

I have tried everything to delete these rows in order to reduce the file size.

Tried a variation of similar direction:
http://www.contextures.com/xlfaqApp.html#Unused

Any suggestions?? Anyone?

"Gord Dibben" wrote:

I never mentioned a right-click and delete row but first you may have to
turn off automatic calculation.

Then follow Shift + End + Downarrow and EditDeleteEntire Row.


Gord

On Fri, 4 Sep 2009 11:14:01 -0700, jabe813
wrote:

Do you happen to know what to do if the below does not work?

I am using Excel 2003. The file is 7MB and needs to be smaller.

I followed your directions below.

When I right click the short cut menu options is "Delete Row"

When I do that it does not ask to 'delete entire row'.
I get an hour glass and in the bottom left corner I get "Calculating Cells:
X%"
When it reaches 100% I still have 65536 rows and then columns that go to IV

Please help :-)


"Gord Dibben" wrote:

Apologies for the lack of direction about re-setting the used range on a sheet.

The reason you have such a large used range is the copying of formulas down and
across far more rows and columns than you may reasonably need.

To reset the used range, go to the bottom of your actual data.

Select the row below and SHIFT + End + DownArrow

EditDeleteEntire Row.

Do same for all columns to the right of your data.

Do this on all sheets.

Now......important part.........Save/Close and reopen.

What is size of workbook now?

To address the original problem, which is having formulas :just in case" you may
want to read up on "Dynamic Ranges" at Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic

Formulas can be entered that will look at only the used range.


Gord

On Wed, 27 Feb 2008 11:23:03 -0800, JoAnn
wrote:

The file size is 20,073KB.

What do you mean by "reset the used range on all sheets"? I haven't done
that since I didn't know I had to and don't know how to do it. I couldn't
find anything in Help on it. Please explain what I have to do.

The only ranges I have set are the sections I'm using for calculations. But
when I format or add a formula to a cell/column/row, I generally copy it down
so it exists in all worksheet rows/columns (I use the keyboard sequence
Ctrl-Shift-DownArrow to select them then paste my formula or formatting
change). That's when I noticed that I had a lot of rows. Could that be
causing a problem?

Thanks for all your help with this!
JoAnn