64,000 extra rows, never a good answer

[kevs1]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I've posted this over years, never a good answer, so I'm persistent.

I have a workbook with 400 rows of info and then 64000 blank rows after.
Normally I will have a few hudred blank rows showing.

I don't want to have them there. I don't know why the occasional workbook shows them, but it's a huge annyoyance because there a scrollbar in which only 3% is for what I'm doing and 97% is blank rolls to scoll to.

I've have near heard a good answer on how to quickly get rid of them thanks!!

 

[Niek Otten]

It may be slightly different on a Mac, but I'm sure the essence is the same:

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

 

[CyberTaz]

Hi Kevs;

I'm sorry you don't like the answer, but it has always been the same and
always will be the same - you can't get rid of the "extra" rows... And if
you don't like the "extra" 65,000 plus I'd recommend that you not upgrade to
Excel 2008 or switch to Excel 2007 on the PC. Those versions supply in
excess of 1,000,000 rows per worksheet - which you can't "get rid of".

That's the way the program is designed and the intent is to accommodate the
vast majority of users whose needs are evidently far different than yours.
The options & suggestions you've been offered in the past represent part of
what the features of the program provide to enable each user to customize
sheets, workbooks & the program itself to their own requirements.

Following the guidelines provided previously you can design a custom sheet
stored in a custom workbook & not have to continue dealing with the
frustration. As long as you choose to not do so you'll just have to contend
with the current default design - I sincerely doubt it will change.

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

 

[Jim Gordon MVP]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I've posted this over years, never a good answer, so I'm persistent.

I have a workbook with 400 rows of info and then 64000 blank rows after.
Normally I will have a few hudred blank rows showing.

I don't want to have them there. I don't know why the occasional workbook shows them, but it's a huge annyoyance because there a scrollbar in which only 3% is for what I'm doing and 97% is blank rolls to scoll to.

I've have near heard a good answer on how to quickly get rid of them thanks!!

Hi,

Try using View > Page Break Preview as your main view of worksheets.

That view limits what you see to the active range.

-Jim

--
Jim Gordon
Mac MVP


WE NEED YOUR HELP!
The number of new Mac users has skyrocketed. That’s excellent. In this
forum all answers come from other users, not from Microsoft employees.
Please help answer questions.

If you see an unanswered question that you know the answer to, please
take just a moment to post the answer. Doing so speeds response time for
people who come here and need fast responses.

If you see an answer that might be wrong or that you can answer better,
hop right in and offer your thoughts. Don’t berate a wrong answer –
we’re all here trying to help each other.

Don’t bother with questions you’re not quite sure about or don’t know about.

Please take just a moment to help fellow Mac users. Your efforts will be
appreciated greatly - especially by those you help.

 

[kevs1]

Jim, page break view, just show the file in microscopic text with a dark blue overlay, making it unreadable.

Bob,
When I click new, workbook, I get a workbook with 98 rows. Great!
Every now and then somethings happens and all 65,000 lines are there below my main stuff. 93% of scroll bar is blank rows.

So only way to fix this is to paste that stuff out into a new workbook?

 

[Bob Greenblatt]

Jim, page break view, just show the file in microscopic text with a dark blue
overlay, making it unreadable.

Bob,
When I click new, workbook, I get a workbook with 98 rows. Great!
Every now and then somethings happens and all 65,000 lines are there below my
main stuff. 93% of scroll bar is blank rows.

So only way to fix this is to paste that stuff out into a new workbook?

No, the way to fix it is a follows, as explained a jillion times in this
forum:
1. check to make sure that there are no formulas, defined names, borders or
shading that refer to cells outside the area where "you" think the data
should be. Fix this if there are some.
2. Select ALL the columns to the right of where the last cell should be.
Drag on the column headers and highlight ALL the way to the extreme right.
Column IV for Excel 2004, column XFD for Excel 2008. Then Edit-DELETE.
3. Select ALL rows below the row of the last cell. Down to row 65536 for
Excel 2004, Row 1048576 for Excel 2008. Then Edit-DELETE.
4. You can certainly use shift and arrow keys to highlight the regions in 2
and 3 above before deleting them. Make sure you use Edit-Delete, NOT the
Delete key.
5. Save the file.
6. Close the file.
7. Open the file. Now, control-End, Edit-go to Special-last Cell, should all
take you to the real last cell, and you scroll bars should be properly
sized.

 

[kevs1]

Bob, lets do one thing at a time, how do I

make sure that there are no formulas, defined names, borders or
shading that refer to cells outside the area where

 

[Bob Greenblatt]

Bob, lets do one thing at a time, how do I

make sure that there are no formulas, defined names, borders or
shading that refer to cells outside the area where

Look in Insert-Name-Define to see if there are any internal name sin the
worksheet; and, if so, what they refer to. Then scroll around to see if you
have borders or shading that go to the sheet edges. A common way where this
may have happened, is if you selected on a row header (number on the extreme
left) and then selected a border.

 

[kevs1]

Bob, you are an expert at this. This is not intuitive and takes a lot of "investigative time"
I'm looking for a quick fast way to delete the extra rows.

My only workaround, I think, it to just copy and paste to a new workbook.

 

[Bob Greenblatt]

Bob, you are an expert at this. This is not intuitive and takes a lot of
"investigative time"
I'm looking for a quick fast way to delete the extra rows.

My only workaround, I think, it to just copy and paste to a new workbook.

That may work. But you can quickly select to the end by highlighting the
last row (or column) and then pressing control-shift-down arrow (or right
arrow). Then Edit Delete these rows (and columns).

 

[kevs1]

It doesn't work Bob, that's what frustrating.

I select row 800,
I control shift down arrow, and now everything below is selected.
I edit, delete, and all 65000 rows below are still there?

Thats why I'm still posting about this issue.

 

[CyberTaz]

OK, here's one you may not have tried...

Click the row number for the row below the last one you want to have
displayed, then go to Format> Row> Height, enter 0, then click OK.

The rows will still exist but perhaps that will provide the effect you want.

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

 

[kevs1]

Bob, thanks, all that did what hide that row, is has the dark blue symbol.

that what I'm saying about Excel.

I have 500 rows filled up, and then 65000 blank rows I don't need to see.
I cannot scroll up and down fluidly the 500 becuase of the other nuisance 65000, the scroll bar flies up and down too rapidly becuase the 500 in relation to the other 65000 is nothing.

 

[CyberTaz]

Sorry I didn't spell it out for you completely - you still need to select
the additional empty rows:

Click the row selector as per previous message

Shift+Command+DownArrow to select all rows to bottom of sheet

Change Row Height to 0 as per previous message

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

 

[kevs1]

Bob, what are you saying here that is different from your 9:17am post?

 

[CyberTaz]

You changed the height for only *1* row. If you want to change them _all_
you have to _select_ them all. The difference between the 2 messages is:

"Shift+Command+DownArrow to select all rows to bottom of sheet"

In the first message I made the mistake of assuming you would know you had
to select the rows you wanted to change.

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

 

[Bob Greenblatt]

It doesn't work Bob, that's what frustrating.

I select row 800,
I control shift down arrow, and now everything below is selected.
I edit, delete, and all 65000 rows below are still there?

Thats why I'm still posting about this issue.

Kevs1,

Excel 2004 has 65536 rows. Period. You can not change this. However, if you
must, you can hide the unused rows so you don't see them. Follow Cybertaz's
instructions for this. But, I don't think this is your problem. If, as you
say, the scroll bar indicates that your worksheet is 65536 rows making
scrolling difficult because you are only using a few hundred rows then you
can also fix this.

Follow my instructions in a previous post about deleting the extra rows.
Yes, the sheet will still show 65K rows when you are done. However, if you
followed my instructions exactly, when the workbook is reopened, moving the
scroll bar to the bottom will bring you to the bottom of your data, NOT the
bottom of the sheet. The exact same situation exists with columns, and you
can use either solution for that also.