Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)

I have a spreadsheet that has roughly 1,000 rows by 15 columns. HOWEVER, rather than the spreadsheet displaying a few more hundred below that, it is displaying 10,000's more rows.  It is a bother rather than a real problem, but i can't use the scroll bar on the right.  Any way to reduce the number of empty rows that are displaying? Thanks

The number of rows is fixed @ 1,048,576 but you can select & hide the ones 
you don't want to have displayed using the Format> Row> Hide command.

The unanswered question, though, is "Why can't you use the Vertical Scroll 
Bar?" There's nothing about the number of displayed rows that disables it in 
any way. It's operation is geared to the number of rows actually used in the 
sheet.

-- 
HTH |:>)
Bob Jones
[MVP] Office:Mac

<HelloFromMass@officeformac.com> wrote in message 
news:59bb7320.-1@webcrossing.JaKIaxP2ac0...
> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have a 
> spreadsheet that has roughly 1,000 rows by 15 columns. HOWEVER, rather 
> than the spreadsheet displaying a few more hundred below that, it is 
> displaying 10,000's more rows. It is a bother rather than a real problem, 
> but i can't use the scroll bar on the right. Any way to reduce the number 
> of empty rows that are displaying? Thanks 

Thanks Bob.  Yes, that is exactly the issue I have, the vertical scroll bar.  It works on the other sheets in the workbook, but, of course it doesn't, on the largest one.  I have tried to see if there is data in a cell out in the hinterlands that is causing this, but doesn't look that way.

I'm afraid I can't do much to help if you don't fully describe the problem
you're having. Vague references such as "... it doesn't [work]" & "... can't
use" don't tell us anything. *Why* can't you use it? Exactly what is the
problem & how is it manifesting itself differently than what you expect?

Regards |:>)
Bob Jones 
[MVP] Office:Mac



On 4/20/10 4:44 PM, in article 59bb7320.1@webcrossing.JaKIaxP2ac0,
"HelloFromMass@officeformac.com" <HelloFromMass@officeformac.com> wrote:

> Thanks Bob.  Yes, that is exactly the issue I have, the vertical scroll bar.
> It works on the other sheets in the workbook, but, of course it doesn't, on
> the largest one.  I have tried to see if there is data in a cell out in the
> hinterlands that is causing this, but doesn't look that way.

Being a Word MVP, this is an irritating habit of Excel that annoys the hell
out of me, too.  The problem appears worse in Excel 2008 because it provides
the new "Big Grid" that goes down a million rows.

That's a highly-prized advance if you want to use a million rows, but it
makes navigating a bit more difficult :-)  Doubly so because we have no VBA.
Normally you use VBA to go to the end and search "up" using "Find" to get
the last row containing data, but we can't do that because Excel 2008 has no
"Search Backwards" command.

Here's how to overcome it.

You need to know three things:

1)  Excel considers "Formatting" to be "content".  For example, if you type
a date in a cell, Excel formats the cell as a date.  If you then delete the
date, Excel still considers that cell has content, because it has a date
format.  To really "empty" that cell, you need to use Edit>Clear>All...

2)  Excel maintains a "Last used cell" range in each worksheet.  But it does
not reset this range UNTIL YOU SAVE.

3)  Excel uses a "sparse matrix" file format to hold down the file size.
That means that while it always "knows about" the big grid of 1,048,576 rows
by 16,384 columns, those rows and columns do not actually "exist" in the
file unless they contain content (either data, or formatting, or both).

To Find the Last Cell in a Sheet:

Click in Cell A1, then use Control + End.  The selected cell will be the one
Excel "thinks" is the lowest one that actually contains anything (even if
you can't see anything there...)

To Find the Last Cell that contains Data

1)  Save the Workbook

2)  Use Command + End

The selected cell will be the last one that actually contains data.

If you know the last data is in Row 1000:

1) Use Control + G to open the GoTo window.

2)  Enter location A1000

3) Now scroll down and identify the last data row by eye.

4) Click in column A of the row below that.

5) Hit Command + Shift + End to select from there to the bottom.

6) Use Edit>Delete>Entire Row to delete the empty rows.

7) Save to reset the "Last used cell" to the area that contains data.

Hope this helps

On 21/04/10 6:44 AM, in article 59bb7320.1@webcrossing.JaKIaxP2ac0,
"HelloFromMass@officeformac.com" <HelloFromMass@officeformac.com> wrote:

> Thanks Bob.  Yes, that is exactly the issue I have, the vertical scroll bar.
> It works on the other sheets in the workbook, but, of course it doesn't, on
> the largest one.  I have tried to see if there is data in a cell out in the
> hinterlands that is causing this, but doesn't look that way.

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:john@mcghie.name