How to shorten a spreadsheet

[Posterizer]

Gang,
I have a spreadsheet with a list, using List Manager. My list ends on
row 1143. However, if I pull the scroll bar down to the bottom of the
spreadsheet, it ends at row 9253. I can click the down arrow and
continue to display more rows.

This is a pain because it makes the scroll bar VERY sensitive to the
slightest movement when I'm trying to navigate my list.

My other spreadsheet files, also containing lists, do not do this.
There are just a few (maybe 50 or less) extra rows below the list, so
the scroll bar is much more useful.

I have looked over the problematic spreadsheet for an errant bit of info
entered in one of the very last cells (row ~9253) but can't find
anything to cause this. I also don't see any preferences related to how
many extra rows to display.

Anyone know how to fix this?

I'm using Excel 11.2 (Office 2004) under OS 10.3.9.

thanks!
_d

 

[Bob Greenblatt]

Gang,
I have a spreadsheet with a list, using List Manager. My list ends on
row 1143. However, if I pull the scroll bar down to the bottom of the
spreadsheet, it ends at row 9253. I can click the down arrow and
continue to display more rows.

This is a pain because it makes the scroll bar VERY sensitive to the
slightest movement when I'm trying to navigate my list.

My other spreadsheet files, also containing lists, do not do this.
There are just a few (maybe 50 or less) extra rows below the list, so
the scroll bar is much more useful.

I have looked over the problematic spreadsheet for an errant bit of info
entered in one of the very last cells (row ~9253) but can't find
anything to cause this. I also don't see any preferences related to how
many extra rows to display.

Anyone know how to fix this?

I'm using Excel 11.2 (Office 2004) under OS 10.3.9.

thanks!
_d

I answered this in the "Can no longer hide rows" thread. I explain how to
fix this there.

 

[Slipface]

I answered this in the "Can no longer hide rows" thread. I explain how to
fix this there.

Still looking for a fix.
_d

 

[Mike Middleton]

_d -

Select rows 1144 through 9353. (Click the row number 1144 on the far left of
the sheet. Use the scroll bar to move down to row 9353. Hold down Shift, and
click row 9353.)

Delete the rows. (Edit | Delete.}

Save the workbook. (Or, Save As if you want to be careful.)

Close the workbook.

Open the saved workbook.

- Mike
www.mikemiddleton.com

 

[Posterizer]

_d -

Select rows 1144 through 9353. (Click the row number 1144 on the far left of
the sheet. Use the scroll bar to move down to row 9353. Hold down Shift, and
click row 9353.)

Delete the rows. (Edit | Delete.}

Nothing happens at this point. The rows remain there, still selected
(faint blue highlighting).

Another thing too, when I scroll to the very last row, it technically
isn't the last row. If I click the down arrow key, it continues
displaying more and more rows. So technically I don't think I can click
on a "last" row.

thanks for the suggestion! Other ideas?
_d

 

[Mike Middleton]

_d -

You wrote: > Other ideas? <

Yes.

Follow all five steps of my original suggestion (below), instead of stopping
after you've followed only the first two.

- Mike
www.mikemiddleton.com

++++++++++++++++++++++++++++++++

Nothing happens at this point. The rows remain there, still selected
(faint blue highlighting).

Another thing too, when I scroll to the very last row, it technically
isn't the last row. If I click the down arrow key, it continues
displaying more and more rows. So technically I don't think I can click
on a "last" row.

thanks for the suggestion! Other ideas?
_d

++++++++++++++++++++++++++++++++

1. Select rows 1144 through 9353. (Click the row number 1144 on the far left
of
the sheet. Use the scroll bar to move down to row 9353. Hold down Shift, and
click row 9353.)

2. Delete the rows. (Edit | Delete.}

3. Save the workbook. (Or, Save As if you want to be careful.)

4. Close the workbook.

5. Open the saved workbook.

 

[Slipface]

_d -

You wrote: > Other ideas? <

Yes.

Follow all five steps of my original suggestion (below), instead of stopping
after you've followed only the first two.

I tried all 5 steps, exactly as they were typed, and it didn't fix it.

In fact, opening the spreadsheet on this computer (home, as opposed to
work) shows row 15872 as the last row visible dragging the scroll bar.
Same version of Excel, but running under OS 10.4.5 here.

_d

 

[Mike Middleton]

_d -

Maybe an MVP's instructions will help:

http://www.contextures.com/xlfaqApp.html#Unused

- Mike

 

[PhilD]

Nothing happens at this point. The rows remain there, still selected
(faint blue highlighting).

(I just posted this in thread "Can no longer hide rows", but I will
repeat it here).

I suspect it DID, actually, do something.

Firstly, if the entire row is selected, then you don't need "delete
row", just "delete", as the selection defines the row.

Secondly, when rows are deleted, the entire spreadsheet below the
deletion moves up and the same number of new rows are added at the
bottom of the sheet, so deleted rows are replaced by the same number of
rows from below the selection. If you delete "blank" rows (rows that
look blank, even if there is some invisible content) and they are
replaced by blank rows, it will look like nothing has happened. The
same number of rows remain selected.

PhilD

 

[Posterizer]

In fact, opening the spreadsheet on this computer (home, as opposed to
work) shows row 15872 as the last row visible dragging the scroll bar.
Same version of Excel, but running under OS 10.4.5 here.

Heh, now it's showing 16911 rows on my work computer. It just keeps
growing and growing. It's almost comical. At least these computers are
fast enough to use PageUp & PageDown keys to move around my list
quickly, without having to use the scroll bar.

_d

 

[Slipface]

Secondly, when rows are deleted, the entire spreadsheet below the
deletion moves up and the same number of new rows are added at the
bottom of the sheet, so deleted rows are replaced by the same number of
rows from below the selection. If you delete "blank" rows (rows that
look blank, even if there is some invisible content) and they are
replaced by blank rows, it will look like nothing has happened. The
same number of rows remain selected.

Well, I'm stumped. I select row 1144, shift click to select row 16911,
and then select Edit | Delete. I would assume that if this was indeed
shortening the spreadsheet by over 15000 rows, I would see the scroll
bar lengthen at least some to reflect the new proportion. But it
doesn't change. If I repeatedly select Edit | Delete on the same
selection, still, nothing apparently happens. Saving, closing,
reopening the file makes no difference.

I'd be glad to send a copy of this file to anyone willing to try a shot
at it on their computer.

_d

 

[JE McGimpsey]

I'd be glad to send a copy of this file to anyone willing to try a shot
at it on their computer.

First try entering

1144:65536

in the Name Box (at the left of the Formula Toolbar) followed by Return,
to select all rows below 1143.

Delete the rows. Save, close and reopen the workbook.

If that doesn't work, send it to me as an archived file (or it will
never get past my filters).

 

[CyberTaz]

Hi -

Just thought I'd throw in a couple of points that _may_ make a difference in
your result This is all implied in the responses you've received, but
perhaps not quite as explicitly pointed out for clarification:

First, the procedure being used to *Delete* the "unused" rows is not going
to "shorten" the sheet. The rows/cells will still be a visible part of the
sheet no matter how many times you 'delete' them. The purpose is to make
sure that any *formatting* as well as content is removed from those cells so
the program understands that they are *not* in use. IOW, by *Deleting* them
the program is actually *replacing* them with fresh, unused rows/cells.

Second, make sure to select *only* cell A1 before you close & save the
workbook. If you leave your active cell in one of the 'unwanted' rows or
leave that range selected when you close & save, it will still be
'remembered' as being an active part of the workbook. As a result, the
Scroll Bar will still allow travel to that area.

If it still presents a challenge, try selecting just the range of content
you want, copy & paste it to a new sheet. Perhaps that will work better.

HTH |:>)

 

[Posterizer]

First try entering

1144:65536

in the Name Box (at the left of the Formula Toolbar) followed by Return,
to select all rows below 1143.

Delete the rows. Save, close and reopen the workbook.

If that doesn't work, send it to me as an archived file (or it will
never get past my filters).

Thanks. I tried that, and also took Taz's advice about selecting cell
A1 after deleting the rows and before saving and closing. Now I'm up to
33000+ rows. Sheesh

I'd be glad to send this to you, but I'm not sure what you mean by
archived file. Would a StuffIt file work?

_d

 

[JE McGimpsey]

I'd be glad to send this to you, but I'm not sure what you mean by
archived file. Would a StuffIt file work?

MacOS 10.4 will create a zip file when the archive option is chosen, but
a Stuffit file will work fine.

 

[Slipface]

MacOS 10.4 will create a zip file when the archive option is chosen, but
a Stuffit file will work fine.

I sent it to you as an archive last Wednesday. Did you receive it?
_d

 

[JE McGimpsey]

I sent it to you as an archive last Wednesday. Did you receive it?

I don't see it - did you send it from the address you post from (I'd
guess not).

I get roughly 1500 spam emails a day, so any message with an attachment
that I haven't explicitly bypassed my filters for gets trashed
automatically...

 

[picardmeister]

I can't help you with your growing spreadsheet, but besides using Page
Up and Page Down keys (which is the preferred method in my book
anyhow), you can navigate to the beginning and end of blocks of cells
by press and releasing End followed by the up/down arrow key.

 

[Slipface]

I don't see it - did you send it from the address you post from (I'd
guess not).

Correct. I don't post my real email address here for the obvious
reasons. I will re-send it from an account in the projects.sdsu.edu
domain. Is that enough information for you to set up an exception in
your filters?

thanks,
_d

 

[JE McGimpsey]

Is that enough information for you to set up an exception in
your filters?

Yes.