"killing cells that are no longer in use"

V

VILLABILLA

Hi,

My question:

When I have a list of data of for example 20000 rows, then when I
delete 10000 rows from the bottom, excel still seems to think that all
20000 cells are in use.

I notice this when I use for example CTR+SHIFT+END (You can use this to
select all cells with a value when you are standing on the first cell
with a value).

How can I let excel forget about these 10000 at the bottom as I am not
using them anymore.

This is important for me as these empty cells cause errors when I try
to import excel sheets into SAP and Access.

Thanks in advance!

Robby
 
K

Ken Johnson

Hi Robby,
I notice it does not happen when I use control + shift + down arrow. I
don't know why there would be a difference between this and control +
shift + end.
Ken Johnson
 
B

Bryan Hessey

Save your worksheet first as a backup name.

If you know that your last row is (say) row 998, then type 999:65536
into the name box and press enter.
This will select the next row down to the end of the worksheet.

Rightmouse within the row selection and Delete those selected rows.

Save, close, and re-open the workbook, it should now reflect the
correct number of used rows (noticed by the size of the scroll bar)
 
B

Bill Martin

VILLABILLA said:
Hi,

My question:

When I have a list of data of for example 20000 rows, then when I
delete 10000 rows from the bottom, excel still seems to think that all
20000 cells are in use.

I notice this when I use for example CTR+SHIFT+END (You can use this to
select all cells with a value when you are standing on the first cell
with a value).

How can I let excel forget about these 10000 at the bottom as I am not
using them anymore.

This is important for me as these empty cells cause errors when I try
to import excel sheets into SAP and Access.

Thanks in advance!

Robby
Click to expand...
------------------------

If you highlight those rows and hit the delete key, it just deletes the data
from the cells. You need to highlight the rows (by dragging the mouse down the
row numbers at the left edge of the spreadsheet for example) and then right
click and select "delete". And then save the file and reopen it.

As an experiment to illustrate this, try adding a couple junk rows of data at
the bottom, and highlight some cells by putting a background color in them.
Then delete by highlighting the rows and hitting the delete key. You'll see the
data disappear but the colored cells remain. They try deleting the rows the
other way and you'll see the entire rows actually disappear as indicated by the
colored cells also disappearing.

Good luck...

Bill
 
Top