Why and How Does Excel Remember The Last Cell?

[Jim J.]

Assume that I create a worksheet that goes to, for example, cell F500. Okay,
now do "File/Save As" to rename & save it. I then delete Rows 101-500.
Now, if I select Ctrl + End in the new worksheet, it will go to F500 – even
though the new worksheet ends at F100 (remember: I deleted rows 101-500).
Some questions:
1) What is this feature called that causes Excel to remember the last cell
(seems like the "last cell that was ever used")?
2) How do you control that feature?
3) In the above example, how can I configure the new worksheet so that Excel
recognizes F100 as the last cell (rather than F500)?
THANKS!

 

[Peo Sjoblom]

Select all rows from after the last visible data and do edit > delete,
do the same for all the columns, SAVE the workbook.
If you have Excel 97 you might need to close and reopen the workbook

 

[T. Valko]

The basic concept is known as the used range.

It serves 2 purposes. It reduces file size and it lets Excel know what cells
need to be calculated in some formulas.

If you had deleted all rows below row 100 and then saved the file again the
used range should have reset.

See this for instructions:

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

If you have data in, say, 100 rows and use a formula like this:

=COUNTA(A:A)

Excel doesn't check every single cell in column A. It will calculate based
on the used range. This saves time and resources.

Biff