"killing cells that are no longer in use"

[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

[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)

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

[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

[Peo Sjoblom]

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

--

Regards,

Peo Sjoblom

"VILLABILLA" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> VILLABILLA
>
>
> ------------------------------------------------------------------------
> VILLABILLA's Profile:
http://www.excelforum.com/member.php...fo&userid=2242
> View this thread: http://www.excelforum.com/showthread...hreadid=486193
>

[Bill Martin]

VILLABILLA wrote:
> 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
>
>
------------------------

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

[Duke Carey]

See this website

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



"VILLABILLA" wrote:

>
> 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
>
>
> --
> VILLABILLA
>
>
> ------------------------------------------------------------------------
> VILLABILLA's Profile: http://www.excelforum.com/member.php...fo&userid=2242
> View this thread: http://www.excelforum.com/showthread...hreadid=486193
>
>