Normally when I drag the vertical scrollbar down to the bottom position
it goes to about line number 150 or so, depending on how much data I
have in that worksheet. But on one worksheet it suddenly and
mysteriously changed to scrolling down to line number 1048576. I don't
know what I did to make it do that. How can I get it back to normal? I
don't like it the way it is because the scrollbar handle is very small
and in normal use stays at the top of the range, making it harder to use
it to scroll up or down within the range of lines where I have data.

Normally when I drag the vertical scrollbar down to the bottom
position it goes to about line number 150 or so, depending on how
much data I have in that worksheet. But on one worksheet it suddenly
and mysteriously changed to scrolling down to line number 1048576. I
don't know what I did to make it do that. How can I get it back to
normal? I don't like it the way it is because the scrollbar handle
is very small and in normal use stays at the top of the range, making
it harder to use it to scroll up or down within the range of lines
where I have data.

Hit the keyboard combo *Ctrl+End* and see where it takes you. It should
select the last row/col of your data. If beyond this then...

If rows:
Select those rows and delete them;

If cols:
Select those cols and delete them;

Save the file;
Hit *Ctrl+End* again to make sure you got it right.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 5/23/2013 8:59 AM, GS wrote:
Normally when I drag the vertical scrollbar down to the bottom
position it goes to about line number 150 or so, depending on how
much data I have in that worksheet. But on one worksheet it suddenly
and mysteriously changed to scrolling down to line number 1048576. I
don't know what I did to make it do that. How can I get it back to
normal? I don't like it the way it is because the scrollbar handle
is very small and in normal use stays at the top of the range, making
it harder to use it to scroll up or down within the range of lines
where I have data.

Hit the keyboard combo *Ctrl+End* and see where it takes you. It
should select the last row/col of your data. If beyond this then...

If rows:
Select those rows and delete them;

If cols:
Select those cols and delete them;

Save the file;
Hit *Ctrl+End* again to make sure you got it right.


Ctrl-End takes me to J1048576. After deleting columns E:J, Ctrl-End
still takes me to J1048576. Similarly deleting rows 100:1048576 has no
effect either. Then I selected all cells and cleared the Bold button,
also the Center Vertically, and a few other things, after which to my
amazement Ctrl-End went to E917504 (xE0000 - what does that show?)

I had noticed before that deleting rows does not change the row to which
you scroll when moving the vertical scroll handle all the way down. In
other words, on a new worksheet, if you enter data into rows 1:100 and
then move the vertical scroll handle all the way down, it will show row
100 at the bottom of the page. If you then delete rows 50:100, you
would expect (hope) that moving the vertical scroll handle all the way
down would result in row 49 being at the bottom of the page. But
instead it still moves down to row 100. I could not find a way to make
it forget that there once was data in rows 50:100.

Is there a difference between cells that were never used and cells that
were used and then cleared, or deleted?

Thanks for your help.

Renny

If you delete rows 50:100 and don't Save then nothing changes because
the UsedRange is not redefined until a save occurs. Using Ctrl+End
should only take you to the last used cell, even when it's empty. If
that happens to be beyond your data then it can be resolved using the
3-step method (delete rows, delete cols, save) I posted.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 5/23/2013 9:15 PM, GS wrote:
If you delete rows 50:100 and don't Save then nothing changes because
the UsedRange is not redefined until a save occurs. Using Ctrl+End
should only take you to the last used cell, even when it's empty. If
that happens to be beyond your data then it can be resolved using the
3-step method (delete rows, delete cols, save) I posted.

Thank you very much. That explains it, and solves my problem.

Cheers.