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
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:
[email protected]