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:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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,791
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
75,764
Office Version
365
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,516
Messages
5,511,750
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top