Control-Shift-End is wrong

combatvolcano

Board Regular
Joined
Jan 13, 2010
Messages
69
Hello,

When I use control+shift+end to try to select to the bottom right portion of a sheet it selects far, far further out than my data.

My data occupies A1:BR9564, when I use control+shift+end I end up at RZ14563.

All other cells contain no data. I can even highlight the blank cells, hit delete and try again and I get the same result.

This

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

is a very important programmatic feature and I need it to work correctly. Any ideas on what is going on here or how to correct it?

Edit: Control Shift Right then Control Shift down does indeed work. What's wrong with xlLastCell???


Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

kdlenort

Board Regular
Joined
May 17, 2010
Messages
102
Select the area of your sheet that you did before to delete the data rather than using delete use clear all.

Post back your results.
:cool:
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
This is just a quirk of Excel..

At one point you HAD data(or formatting) that was all the way out to column RZ and row 14563.

Deleting the data/formatting from those cells unfortunately does not reset what Excel thinks is the last used cell.

Usually Saving the file resets it, or this line of code

ActiveSheet.UsedRange

That will reset it.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
What's wrong with xlLastCell???

Hi

I don't use SpecialCells(xlLastCell), you cannot trust it. Excel somehow retains the memory of the last cell in the worksheet that was used, even if it never contained any data, it just has for ex. to have been formatted.

The Last Cell is not updated. Even if the last cell is cleared and no longer contains anything, excel still thinks it's the last cell and so it's risky to use it. There are some workarounds to reset the UsedRange (Jonmo posted one) but I've heard over the years that they don't work in a version or another. The only safe way that I know to reset the used range is to save the workbook, which is not practical and makes no sense in most cases.

Bottom line, in my opinion you should not use SpecialCells(xlLastCell).
 

combatvolcano

Board Regular
Joined
Jan 13, 2010
Messages
69

ADVERTISEMENT

Thanks for the replies!

I hear you all on the 'remembers last cell' but nothing, no cursor, no data, nothing was ever at that cell. It was never even visible on the screen. Nothing was ever even at that address on any worksheet or recently opened workbook.

I'm going to sneer at this as an excel PROGRAM BUG (read: flaw) and just use down, right from now on.

Thanks guys! have a good weekend
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
There's a fine line here to understand..

When CTRL SHIFT END goes to RZ14563
That does not necessarily mean that cell had data or formatting in it at one time.
What it actually means, is RZ14563 is the intersection of the last used column and row.

So there may have been something in RZ1, and something in A14563
That would cause RZ14563 to be registered as the lastcell, even though RZ14563 was never actually used.


So strictly speaking, if the END is going to RZ14563
It means that at one time or another, some cell in column RZ (in any row of that column) was used
And at some time or another, some cell in Row 14563 (in any column of that row) was used.
Not necessrilay that specific cell RZ14563
 
Last edited:

combatvolcano

Board Regular
Joined
Jan 13, 2010
Messages
69

ADVERTISEMENT

I hear ya Jonmo, unfortunately none of those things are true... I simply didn't use a row that far down or a column that far out on that sheet or any sheet in my book or any other book... really, I'd love to know the actual cause of this, but it's not because I used that range anywhere at all.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Do you ever INSERT Rows and/or columns?

Lets say your last used row/column is Z100

But then you delete some data, so it's only out to say M50 now.
But, last cell is still Z100

But then you INSERT 100 rows
Your actual last used row is now 150, BUT last cell is now M200


You can see this by experimenting with a new blank book

put anything in cell E10 or whatever.
Then Highlight ALL cells, and right click DELETE.

Select A1 and press CTRL + END
It still goes out to E10.

Now insert a row and column.
CTRL END now goes to F11


Yes it's wierd, and shouldn't happen, but it does.

Saving the file will clear it.
Or that line of code I suggested will clear it.

And as pgc01 said,
It's really best to just not depend on LastCell
There are better more accurate methods of determining that last used row/column.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
Why not try CTRL+SHIFT+*?

PS Can you 100% be sure that you (or someone else) didn't use/format/whatever the rows/columns/cells that shouldn't be included?

Definitely no code that didn't quite seem to run as expected?

You might also want to be careful using Select/Selection and add in some worksheet references.

VBA might not be agreeing with you about what is actually selected and without worksheet references it could be looking at the wrong worksheet(s).
 

combatvolcano

Board Regular
Joined
Jan 13, 2010
Messages
69
I hear ya Jonmo, unfortunately none of those things are true... I simply didn't use a row that far down or a column that far out on that sheet or any sheet in my book or any other book... really, I'd love to know the actual cause of this, but it's not because I used that range anywhere at all.

Anyone know where in the environment xllastcell is stored? (or what it uses to get it's ranges when called?)

If I could track the numbers out of excel as they change I could possibly track this down.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,816
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top