Suppress unused columns and rows?

[ALFE Engelbach]

I have spreadsheet that uses 280 rows, yet when I scroll down using the
scroll bar only the first inch or so represents the used rows. The rest
of the sheet is just empty rows. When I drag the slider down to the
bottom of the scroll bar I get to row 7756.
So, I am trying to figure out how to suppress all or most of the 7400
odd empty rows that keep showing up below the used rows. I have see
other people create spreadsheets that show only a few rows below the
used ones, yet I can't find anything in the Excel Help about how to
suppress unused rows.
Can anyone suggest a way to do this?

Thanks in advance.
ALFE

 

[Bob Greenblatt]

I have spreadsheet that uses 280 rows, yet when I scroll down using the
scroll bar only the first inch or so represents the used rows. The rest
of the sheet is just empty rows. When I drag the slider down to the
bottom of the scroll bar I get to row 7756.
So, I am trying to figure out how to suppress all or most of the 7400
odd empty rows that keep showing up below the used rows. I have see
other people create spreadsheets that show only a few rows below the
used ones, yet I can't find anything in the Excel Help about how to
suppress unused rows.
Can anyone suggest a way to do this?

Thanks in advance.
ALFE

This happens when Excel "thinks" the sheet has more rows (or columns) than
you do. This may happen with a defined name that refers to extra rows (or
columns), or you applied some formatting to the extra rows (or columns) and
then deleted something.

To find where Excel "thinks" the end of the sheet is, press control-end.
This will select the cell Excel "thinks" is the end. If this is different
from where you think the end of the sheet should be, and you are sure that
no names refer to these cells, and that there's nothing relevant in them,
you can delete them as follows:

For rows, click on the first row number on the left side of the sheet, which
is just below where you think the data ends, and drag downward selecting
blank rows up to and a little beyond Excel's last row. With these rows
selected, Select Edit-Delete. Likewise, you should do the same thing for all
columns to the right of your data. After the rows and columns have been
deleted, SAVE and CLOSE the workbook. When you reopen it, both you and Excel
should now agree on where the end is. Check by pressing control-End again to
be sure. You may also find that the workbook takes significantly less disk
space and may be a bit more responsive.

 

[CyberTaz]

Select the rows to be "suppressed" , then go to Format>Row>Hide. Next
select the additional coumns and Format>Column>Hide.

BTW- There are 65,536 rows, so you actually want to hide 65,256 of
them, so here's a shortcut to select them quickly & easily: Click the
row number for the topmost row to be hidden, then Shift+Cmd+DownArrow.
Once they are hidden (as above), click the heading of the leftmost
column to be hidden and use Shift+Cmd+RightArrow to select the extra
columns & then hide them.

You may also want to look into Protection of the sheet to prevent
others from unhiding the rows & columns.

 

[ALFE Engelbach]

Many thanks Bob! That worked. And It is significantly faster now.

I discovered that Excel seemed to "think" that the end of the used
rows was far below (about row 8000) where it actually (in row 300) was
due to some comments I created in each of the cells in the top row of
that worksheet. I had to delete the comments to get the unused rows to
go away. I still want to use comments, but want to avoid this problem
in the future, and am wondering how to do that.

To be clear, I noticed that, when I hovered the mouse over the comment
triangle, a) it took a LONG time to open the comment, and b) the
comment field it finally displayed was long and narrow and went nearly
to row 8000. Edit/dragging it around didn't fix it, so I just deleted
the comment. I'm not sure how it got that way because I certainly
didn't do that on purpose.
I think I had frozen panes at the time I created the comments - might
that have something to do with it?

Thanks again for the help.
ALFE

 

[Bob Greenblatt]

Many thanks Bob! That worked. And It is significantly faster now.

I discovered that Excel seemed to "think" that the end of the used
rows was far below (about row 8000) where it actually (in row 300) was
due to some comments I created in each of the cells in the top row of
that worksheet. I had to delete the comments to get the unused rows to
go away. I still want to use comments, but want to avoid this problem
in the future, and am wondering how to do that.

To be clear, I noticed that, when I hovered the mouse over the comment
triangle, a) it took a LONG time to open the comment, and b) the
comment field it finally displayed was long and narrow and went nearly
to row 8000. Edit/dragging it around didn't fix it, so I just deleted
the comment. I'm not sure how it got that way because I certainly
didn't do that on purpose.
I think I had frozen panes at the time I created the comments - might
that have something to do with it?

Thanks again for the help.
ALFE

Yes, I think it might have. I've seen this before, and I think it had to do
with frozen panes. Deleting the comment and re entering it, or manually
fixing each comment one at a time will fix the problem. Inserting a new
comment should not be a problem. Hover the mouse over them every once in a
while to make sure things are OK.