Link to home
Create AccountLog in
Avatar of Robert Berke
Robert BerkeFlag for United States of America

asked on

Masochists only: Empty workbook, but Control End goes to FQ1.

From time to time I find weird worksheets where Control End goes to an unexpected cell.

Copying the entire worksheet to a new worksheet also copies the weirdness.

The only way to get rid of the problem is to select the entire used range and copy it into a new sheet and delete the old sheet.

Can anybody find a different way to solve the problem?

It seems to originate when a large worksheet uses autofilter, and then turns off autofilter.  It might also be related to deleting a pivot table.
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

There's empty cells and there's blank cells. They are not the same thing. Ctrl+End goes to the start of the empty cells. Empty cells are those that contain nothing. They are literally undefined in the xml. Blank cells on the other hand may very well contain non-printable characters, such as an empty string.

Another thing that may be happening is the "dimension" (or UsedRange) of the worksheet may not be reset after clearing data. Excessive formatting if often the cause in this instance (see:

Either way, you get rid of them by deleting all rows below your data, then save, close and reopen.
Not for points: In addition to Wayne's suggestion, removal of columns beyond the known data area will reset the dimension.

Rows and Columns have to be removed rather than just deleted:

Home Tab > Cells Group > Delete > Delete Sheet Rows / Delete Sheet Columns

Keyboard shortcut "Ctrl & -", Entire Rows / Entire Columns.

Avatar of Robert Berke


Posting theoretical solutions is great, but to be a true masochist you must open the spreadsheet and verify that your theories work.

By the way, the spreadsheet was created with excel 2010 under windows 10, but it has the same problem under window 7 with excel 2013.  If it works better on any of your computers I would like to know.

The problem is that cells a1 through FQ1 look like they have blank cells, but every attempt to delete them fails.

I ran XSFormatCleaner, and I manually deleted every row, and every column and cleared contents and deleted all cell references(using Name Manager), deleted shapes, deleted conditional formatting . I have run out of things to search.

The sheet really should be empty, but goto special still shows blank cells in a1:fq1. (home tab;Editing group;Find and Select drop down)
I tried every other radio button in goto special and all show an empty worksheet (except blanks).

I am probably wasting my time with this post, but I have a home brew "Sheet Trim" macro which is like XSFormatCleaner on steroids.   I have been tweaking it for at least 7 years, and I would love to make it handle these kind of weird spreadsheets.
To get a true answer to your question, you need to upload the workbook.....
The one step which is crucial to resetting Used Range after doing all the Format Cleaning and Row or Column Deleting....

Save the file

There is no mention in your comment about saving.
Geez !!  I thought I had uploaded it.  My sincere apologies.
And, yes I always saved the file, closed and reopened the file.
No worries, I wasn't having a moan; just going along with your triste.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I forgot about the .zip trick, which I haven't used in a long time. Thanks for the brilliant idea.  

I just tried changing FR1 to A1 using notepad (I had to copy the xml out of the .zip file the back into the .zip file). After renaming back to .XLSX, Excel said it found unreadable data and offered to recover it. After recovery FR1 was still the last cell.  
So, there is clearly something wrong which is less obvious.

When I have the time, I will try something more thorough. I will
open BadBook,
add a new worksheet,
copy the used range from BadSheet to NewSheet
delete BadSheet
save book as GoodBook.
change both GoodBook and BadBook to .zip
compare GoodBook and BadBook xml to see what else is different.  

But, that is a lot of work, so I am closing this question now.  

By the way, my SheetTrim is kind of a hobby for me. It often encounters similar weirdness where last cell cannot be reset.  But the weirdness usually went away once the workbook was closed which made it extremely hard to repeat. So, I am thrilled to now have a repeatable workbook, so I can debug at my leisure.

Right now my program says "SheetTrim was not entirely successful - try closing your workbook and reopening it.".  If I ever figure out an XML manipulation that "corrects" the problem, SheetTrim would be changed to say "Do you want SheetTrim to close your workbook and fix the problem?"  

Anyway, thanks for your help.

Bob (rberke)
Turns out that the entire problem was line 11 of the XML needed to be deleted.
The other lines with FQ could keep their "incorrect" values.

i deleted line 11 and saved the xml and renamed zip to .xlsx.
The file still opened with the wrong control end BUT, any Once line 11 was deleted, and the zip file renamed to xlsx.  Now control end selects the correct cell.  

I have changed my sheet truncate program to give this advice.

Sheet only partially truncated. Click "OK" and the sheet will automatically close, and be reopen with full truncation.".

Right now clicking the button, only closes and opens the workbook -- it does not fix the xml.  That is for a later day.
Putting 2 + 2, maybe the 176 in line 11 needs amending to 1 rather deleting whole line; col FS is column 176 if I work it out correctly.
Now, here is the big question:  Is there some way I can fix this directly in vba?  
For instance, I just tried hiding A:fz, then unhiding it.  That didn't work but perhaps there is some other trick.

I may open another question on this subject.