peters022697
asked on
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
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
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.
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.
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.
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()
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
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
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
Do the same for Columns G to IV.
This must be working.......
GL
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
THANKS
Points will be awarded to cri in 7 days unless peters gives viable objection.
amp
Community Support Moderator
amp
Community Support Moderator
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.)
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.)
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.