How do I move the last cell on a EXCEL 97 worksheet (indicated by CTRL+END )

Using Excel 97.

I have a worksheet with the last cell at F:1590
I know this as CTRL+END puts me at this cell.

I have nothing in this cell and want to make the "last cell" F:100
ie I want CTRL+END to put me at F:100.

I've tried deleting rows/cells etc but still CTRL+END puts me at F:1590

please help
Who is Participating?
criConnect With a Mentor Commented:
peters, you found the problem ? See my comment 08/14/2001. And, as stated there, you do not have to close the workbook, saving the workbook is enough.
Highlight the text up to F100.  Name this range.

Now hit CTRL END  - does it put you at F100? or do you sitll go to F1590?

When you say you deleted rows  - did you delete line 1590?  When Excel goes to a cell with CTRL END, it usually means there is something in that cell.  Try highlighting that row and clear all.
Man, I've tried everything.
I guess the tag that keeps the last edited cell is in the same place that keeps the counter for the number of sheets created.

I'll keep looking.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Unfortunately, at times no matter what you do Excel will not reset the last cell to a cell before another, i.e. one closer to Home (A1) once it has settled on a last cell on a sheet.  This is even after you have selected the first row you want to clear, press ctrl+shift+down arrow and cleared all rows, and have also selected the first column you want to clear, press ctrl+shift+right arrow and cleared all columns.  In your case that would be rows 101 thru 65536 and columns G thru IV.

If after clearing or deleting all rows it still does not work, then Excel still thinks that there is something in the cell.  Your best alternative is to highlight all of the data from that sheet (cells A1 thru F100 in your case), copy and paste that data to a new worksheet, then delete the old one.  You will now be able to press ctrl+end and go to the last cell that you desire.  The program will reset the last cell accordingly as you move about and add data to this new sheet from this point forward.

Hope this helps.
...but dont dare grab any extra cells or Excell will think the last copied cell is the destination for Control-End.
Reason is that Excel considers empty cells with non standard formats as within the used range and will update this only with a save. Therefore delete the rows, save the workbook and try again. If this works, I have a macro which will clear all formats after the "RealLastCell". If interested, please ask.
Resetting Excel's used range (97/98/2000)

We recently told you how to navigate to the ends of an Excel worksheet
using the [Ctrl][Home] and [Ctrl][End] shortcut keys. You may have
noticed a bug in Excel that causes it to continue selecting the
same end point for a worksheet even if you've deleted the data
that was at that point.  To fix the problem, Microsoft recommends
running a simple VBA procedure. To create the macro, open the workbook
you want to create the procedure in and press [Alt][F11] to open
the Visual Basic Editor (VBE). Then, choose Insert | Module from
the menu bar and enter the following code:

Sub Reset_Range()
End Sub

Finally, save the module and return to Excel.  Now, you can run
the Reset_Range macro whenever you want to refocus Excel on just
the occupied cells in a worksheet.  

You should be aware of issues with the macro that Microsoft doesn't
point out.  First, running it causes you to lose the ability to
undo prior actions.  Also, if you run the Reset_Range macro after
clearing cells--as opposed to deleting them--you may find that
Excel continues to move its cell selector to the wrong location.
In such cases, you can usually solve the problem by selecting the
appropriate rows or columns, choosing Edit | Delete from the menu
bar and re-running the macro.

Here's another useful Microsoft Excel tip from Element K Journals,
your number one source for software tips and techniques
MurpheyApplication ConsultantCommented:
Try to delete all the rows (not only the contents) from row 101 to the end (65536), by selecting it end click on >Edit >Delete.

Do the same for Columns G to IV.

This must be working.......

peters022697Author Commented:
Have found the problem.

The "last cell" will reset to the desired cell ONLY AFTER CLOSING AND REOPENING the workbook.

Deleting relevant rows and columns AND THEN CLOSING the workbook seems to work fine. Thanks to those who contributed.
try to copy the cells from first cell untill F:100 AND then copy them in a different file and save it .. then try it in the new file.

Points will be awarded to cri in 7 days unless peters gives viable objection.

Community Support Moderator
Points awarded to cri.

community support moderator

(cri, I most assuredly appreciate your notes of appreciate, but you double-up my inbox...LOL. No need to post thanks. But good to see your name a lot, even if it's older stuff.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.