Excel 2007 Delete Range ctl+End

T

TKS_Mark

I paste a couple hundred rows of data in the same sheet as another "table".
When I've made sure the columns are the same by deleting extra data, I cut
and paste that rows into the "table" above. So there's no data below the
table since it has been cut.

When I click ctrl+end, the mouse moves to where the cut data used to end.
Even if I go back and delete all those rows, ctrl+end seems to remember the
old end point? How can I reset this? Why does it still happen after all
these years?
 
B

Beth Melton

H

Harlan Grove

First, you'd be better off asking Excel-specific questions in Excel-
specific newsgroups rather than this newsgroup. I'm cross-posting to
microsoft.public.excel.misc and setting Followup-to to that newsgroup.

TKS_Mark said:
I paste a couple hundred rows of data in the same sheet as
another "table". When I've made sure the columns are the same by
deleting extra data, I cut and paste that rows into the "table"
above. So there's no data below the table since it has been cut.
Click to expand...

One alternative would be pasting into a new, blank workbook, deleting
columns there, then moving what's left from there to below the
original destination table.
When I click ctrl+end, the mouse moves to where the cut data used
to end. Even if I go back and delete all those rows, ctrl+end
seems to remember the old end point? How can I reset this? . . .
Click to expand...

Not sufficient to delete rows. You should also delete columns to the
right of what you believe the used range to be. And sometimes you'll
also need to save the workbook before Excel will reset the
worksheet's .UsedRange to what you'd consider its used range should
be. And in exceptionally rare cases, you'd need to close the workbook,
exit Excel, relaunch Excel and reopen the workbook before the used
range would be reset as you'd expect it.
Why does it still happen after all these years?
Click to expand...

Because the .UsedRange property of the Worksheet class is sticky. It'd
slow down Excel's overall operations if it had to check whether to
reduce .UsedRange every time the user did anything that could
affect .UsedRange. For most things, the actual state of .UsedRange
doesn't much matter. The only thing clearly affected by is is the
width/height of the horizontal and vertical scroll bar sliders.
 
T

TKS_Mark

Where is the Excel-specific newsgroup?

Harlan Grove said:
First, you'd be better off asking Excel-specific questions in Excel-
specific newsgroups rather than this newsgroup. I'm cross-posting to
microsoft.public.excel.misc and setting Followup-to to that newsgroup.



One alternative would be pasting into a new, blank workbook, deleting
columns there, then moving what's left from there to below the
original destination table.


Not sufficient to delete rows. You should also delete columns to the
right of what you believe the used range to be. And sometimes you'll
also need to save the workbook before Excel will reset the
worksheet's .UsedRange to what you'd consider its used range should
be. And in exceptionally rare cases, you'd need to close the workbook,
exit Excel, relaunch Excel and reopen the workbook before the used
range would be reset as you'd expect it.


Because the .UsedRange property of the Worksheet class is sticky. It'd
slow down Excel's overall operations if it had to check whether to
reduce .UsedRange every time the user did anything that could
affect .UsedRange. For most things, the actual state of .UsedRange
doesn't much matter. The only thing clearly affected by is is the
width/height of the horizontal and vertical scroll bar sliders.
Click to expand...
 
B

Beth Melton

While Bob supplied the link for the Excel newsgroup, both Harlan and I
provided the answer you are looking for in the event you missed it. :)

Please post all follow-up questions to the newsgroup. Requests for
assistance by email cannot be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Coauthor of Word 2007 Inside Out:
http://www.microsoft.com/MSPress/books/9801.aspx#AboutTheBook

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/
 
Top