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
<HelloF...@officeformac.com> wrote in message
news:59bb7...@webcrossing.JaKIaxP2ac0...
Regards |:>)
Bob Jones
[MVP] Office:Mac
On 4/20/10 4:44 PM, in article 59bb7...@webcrossing.JaKIaxP2ac0,
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 59bb7...@webcrossing.JaKIaxP2ac0,
"HelloF...@officeformac.com" <HelloF...@officeformac.com> wrote:
--
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:jo...@mcghie.name