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 wrote in message news:[email protected]. [email protected] 20/4/2010, 13:44 น. 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.
![]() ![]()
1,048,576 rows by 16,384 columns. Column width. 255 characters. 1,026 horizontal and vertical. Total number of.
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 EditClearAll. 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 EditDeleteEntire 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 [email protected], ' 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: blockparty 8/1/2014, 15:08 น.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |