Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2001-08-14
12
Medium Priority
?
740 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 400 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

636 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