Excel 97 (End-of-worksheet marker)

[Frank W]

The CTRL/END key combination will goto and select the 'last' cell in the worksheet. Usually this is the cell that you would expect it to be, but sometimes the worksheet becomes 'inflated' and the 'last' cell inconveniently appears several columns or rows later than it should be.
How can the CTRL/END marker position be adjusted to show the real end-of-worksheet cell?

Deleting intermediate blank rows or columns does not do the job. Usually I get round the problem by selecting the required block of cells and copying onto a blank worksheet, but there must be an easier way?

Regards...........Frank W

[Bengie]

Hello Frank

When you say 'inflated', what actually do you mean. Are you saying that the end cell that ctr/end is taking you has 'not' been formatted in any way, that the cell that gets highlighted is not actually part of your worksheet?

I have the Genuine Microsoft Excel book here, but it is huge, and I have only used it for the parts that I needed instruction on, so don't really know my way around it, but am willing to see what I can find for you.

[Frank W]

Hi Bengie
As a specific example, a current Excel worksheet for a FreeCEN transcription uses Rows 1-4135 and Columns A-Z, so the CTRL/END key combination should cause the Z4135 cell to be selected as 'end-of-worksheet' cell.
In fact it selects Z4506, so the worksheet has become inflated by another
371 rows, with no visible content in their cells. If these rows are deleted or edited to clear all their contents, it makes no difference to the cell Z4506 selected by CTRL/END
I do have the earlier version Excel books, and the Dummies Guide also, but its not worth spending the time on searching through them. I thought I'd enquire on the Forum in case anyone had an instant answer.
Thanks for your interest anyway.
Regards........Frank W

[Terry]

Frank, Try putting your question on this forum:- http://www.ozgrid.com/forum/

I've had super help from them about Excel on quite a few occassions. The forum help is free, although a lot of the site is commercial

[Neil Wilson]

Not having Excel at home so I can not try this.

Is it something to do with the page set up found under File? Where you can define the size of the sheet?

[Frank W]

Hi Terry
I've registered on the ozgrid site, and will pop the question tomorrow. Did'nt find the answer in their FAQs though there was an item on restricting the scrollable portion of the worksheet. It also wipes out the CTRL/END command as well, and sets the worksheet size to be uneditable.

Hi Neil
I think the Page setup item in the File menu refers to setting the size of a page on the printer and not to resizing the worksheet itself. I had tried that command but as I have no printer connected at present it would not do anything at all.

Regards.......Frank W

[Neil Wilson]

good luck Frank, keep us posted.

[Duncan Cameron]

The CTRL/END key combination will goto and select the 'last' cell in the worksheet. Usually this is the cell that you would expect it to be, but sometimes the worksheet becomes 'inflated' and the 'last' cell inconveniently appears several columns or rows later than it should be.
How can the CTRL/END marker position be adjusted to show the real end-of-worksheet cell?

Deleting intermediate blank rows or columns does not do the job. Usually I get round the problem by selecting the required block of cells and copying onto a blank worksheet, but there must be an easier way?

Regards...........Frank W
Hi Frank

You need to save the worksheet after deleting the rows and columns. Then CTRL-END should go to the final "real" cell.

[Frank W]

good luck Frank, keep us posted.
Hi Neil
I've had two replies on Ozgrid Forum from "carlmack".
He quoted link : http://www.mvps.org/dmcritchie/excel/lastcell.htm
This gives a comprehensive and detailed review of other reference sources, but needs some time to assimilate.
His second reply suggests a simple macro to try (have'nt done this yet)
You can find this and the rest of the thread at:
http://www.ozgrid.com/forum/showthread.php?t=56384&goto=newpost
Regards.......Frank W
PS If you thought B_G Forums were busy you'll be amazed by the traffic on Ozgrid XL Help/VBA Help

[Neil Wilson]

Interesting reading Frank, I haven't used a macro for years so I will avoid it. Also I think my employers won't be happy if I used any code from outside (know this from the web work I do, it is ok for IT but not for others.)