Solved

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

Posted on 2001-08-14
12
717 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:peters022697
  • 2
  • 2
  • 2
  • +6
12 Comments
 
LVL 4

Expert Comment

by:tituba2
ID: 6386732
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.
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 6386937
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.
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 6386944
peters,

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.
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 6386955
...but dont dare grab any extra cells or Excell will think the last copied cell is the destination for Control-End.
0
 
LVL 13

Expert Comment

by:cri
ID: 6387035
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.
0
 
LVL 1

Expert Comment

by:jbehm
ID: 6389889
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()
 ActiveSheet.UsedRange
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6391690
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.......


GL
0
 

Author Comment

by:peters022697
ID: 6395283
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.
0
 
LVL 13

Accepted Solution

by:
cri earned 100 total points
ID: 6396293
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.
0
 

Expert Comment

by:larouge
ID: 6396593
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.

THANKS
0
 

Expert Comment

by:amp072397
ID: 6709175
Points will be awarded to cri in 7 days unless peters gives viable objection.

amp
Community Support Moderator
0
 

Expert Comment

by:amp072397
ID: 6725271
Points awarded to cri.

amp
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.)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now