Can't delete rows

Thanks, that explains it! The rows are blank.

My reason for trying to delete them ... the spreadsheet is not set as
"shared" but is being stored in MS Sharepoint & for some reason it takes 1-2
minutes to open from there (opens much faster on my PC).

Thought the number of blank rows was the reason. Now that I know it's
standard for Excel, I'll check our IT department & the Sharepoint Newsgroup
to try to find out why this particular workbook opens so slowly when others
don't.

If you have any idea why that would happen (from an Excel standpoint),
please let me know. I don't have any macros in it (yet) & there are only
about 80 rows of data in one sheet & 20 in another.

Also ... there will be a 3rd "archive" sheet eventually so the workbook
will be growing quite large with time so if there's a limit to overall
workbook size, please let me know.

Thanks for your help!
JoAnn


"Gord Dibben" wrote:

> Rows of what? Data or just blank rows?
>
> An Excel worksheet has 65536 rows and 256 columns(2007 version has over one
> million rows and 16,384 columns)
>
> None of these can be deleted.
>
> Data can be deleted or cleared but the rows and columns remain the same number.
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn <JoAnn DeleteThis @discussions.microsoft.com>
> wrote:
>
> >Anyone have any idea why I wouldn't be able to delete rows in a worksheet?
> >It doesn't seem to be protected for row deletion.
> >
> >I am selecting the whole row (from the left side of the sheet) & then going
> >to the end of the sheet to grab the bottom.
> >
> >I have over 6,000 rows in this one sheet! No idea how it happened or how I
> >can get rid of them (other than copy/paste what I want to save into a new
> >worksheet).
> >
> >Thanks!
> >JoAnn
>
>

What is the file size now that you have reset the used range on all sheets?

Should be very small if your data is as you state.

I don't work with SharePoint at all so can't speak to that.

Overall workbook size is a function of the resources you have available.

I have heard of workbooks at the 100Mb area.


Gord

On Wed, 27 Feb 2008 05:27:02 -0800, JoAnn <JoAnn DeleteThis @discussions.microsoft.com>
wrote:

>Thanks, that explains it! The rows are blank.
>
>My reason for trying to delete them ... the spreadsheet is not set as
>"shared" but is being stored in MS Sharepoint & for some reason it takes 1-2
>minutes to open from there (opens much faster on my PC).
>
>Thought the number of blank rows was the reason. Now that I know it's
>standard for Excel, I'll check our IT department & the Sharepoint Newsgroup
>to try to find out why this particular workbook opens so slowly when others
>don't.
>
>If you have any idea why that would happen (from an Excel standpoint),
>please let me know. I don't have any macros in it (yet) & there are only
>about 80 rows of data in one sheet & 20 in another.
>
>Also ... there will be a 3rd "archive" sheet eventually so the workbook
>will be growing quite large with time so if there's a limit to overall
>workbook size, please let me know.
>
>Thanks for your help!
>JoAnn
>
>
>"Gord Dibben" wrote:
>
>> Rows of what? Data or just blank rows?
>>
>> An Excel worksheet has 65536 rows and 256 columns(2007 version has over one
>> million rows and 16,384 columns)
>>
>> None of these can be deleted.
>>
>> Data can be deleted or cleared but the rows and columns remain the same number.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn <JoAnn DeleteThis @discussions.microsoft.com>
>> wrote:
>>
>> >Anyone have any idea why I wouldn't be able to delete rows in a worksheet?
>> >It doesn't seem to be protected for row deletion.
>> >
>> >I am selecting the whole row (from the left side of the sheet) & then going
>> >to the end of the sheet to grab the bottom.
>> >
>> >I have over 6,000 rows in this one sheet! No idea how it happened or how I
>> >can get rid of them (other than copy/paste what I want to save into a new
>> >worksheet).
>> >
>> >Thanks!
>> >JoAnn
>>
>>

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


--
JoAnn


"Gord Dibben" wrote:

> What is the file size now that you have reset the used range on all sheets?
>
> Should be very small if your data is as you state.
>
> I don't work with SharePoint at all so can't speak to that.
>
> Overall workbook size is a function of the resources you have available.
>
> I have heard of workbooks at the 100Mb area.
>
>
> Gord
>
> On Wed, 27 Feb 2008 05:27:02 -0800, JoAnn <JoAnn.DeleteThis@discussions.microsoft.com>
> wrote:
>
> >Thanks, that explains it! The rows are blank.
> >
> >My reason for trying to delete them ... the spreadsheet is not set as
> >"shared" but is being stored in MS Sharepoint & for some reason it takes 1-2
> >minutes to open from there (opens much faster on my PC).
> >
> >Thought the number of blank rows was the reason. Now that I know it's
> >standard for Excel, I'll check our IT department & the Sharepoint Newsgroup
> >to try to find out why this particular workbook opens so slowly when others
> >don't.
> >
> >If you have any idea why that would happen (from an Excel standpoint),
> >please let me know. I don't have any macros in it (yet) & there are only
> >about 80 rows of data in one sheet & 20 in another.
> >
> >Also ... there will be a 3rd "archive" sheet eventually so the workbook
> >will be growing quite large with time so if there's a limit to overall
> >workbook size, please let me know.
> >
> >Thanks for your help!
> >JoAnn
> >
> >
> >"Gord Dibben" wrote:
> >
> >> Rows of what? Data or just blank rows?
> >>
> >> An Excel worksheet has 65536 rows and 256 columns(2007 version has over one
> >> million rows and 16,384 columns)
> >>
> >> None of these can be deleted.
> >>
> >> Data can be deleted or cleared but the rows and columns remain the same number.
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Tue, 26 Feb 2008 12:55:05 -0800, JoAnn <JoAnn.DeleteThis@discussions.microsoft.com>
> >> wrote:
> >>
> >> >Anyone have any idea why I wouldn't be able to delete rows in a worksheet?
> >> >It doesn't seem to be protected for row deletion.
> >> >
> >> >I am selecting the whole row (from the left side of the sheet) & then going
> >> >to the end of the sheet to grab the bottom.
> >> >
> >> >I have over 6,000 rows in this one sheet! No idea how it happened or how I
> >> >can get rid of them (other than copy/paste what I want to save into a new
> >> >worksheet).
> >> >
> >> >Thanks!
> >> >JoAnn
> >>
> >>
>
>

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

Edit>Delete>Entire 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 <JoAnn.DeleteThis@discussions.microsoft.com>
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

That did it!!! It's down to 93KB now.
Thank you so much for all your help!

--
JoAnn


"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
>
> Edit>Delete>Entire 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 <JoAnn DeleteThis @discussions.microsoft.com>
> 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
>
>

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!