Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2016-09-14
Medium Priority
Last Modified: 2016-09-18
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.
Question by:rberke
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41798990
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: https://support.microsoft.com/en-au/kb/244435)

Either way, you get rid of them by deleting all rows below your data, then save, close and reopen.
LVL 33

Expert Comment

by:Rob Henson
ID: 41799647
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.


Author Comment

ID: 41800078
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 33

Expert Comment

by:Rob Henson
ID: 41800083
To get a true answer to your question, you need to upload the workbook.....
LVL 33

Expert Comment

by:Rob Henson
ID: 41800091
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.

Author Comment

ID: 41800170
Geez !!  I thought I had uploaded it.  My sincere apologies.
And, yes I always saved the file, closed and reopened the file.
LVL 33

Expert Comment

by:Rob Henson
ID: 41800180
No worries, I wasn't having a moan; just going along with your triste.
LVL 33

Accepted Solution

Rob Henson earned 2000 total points
ID: 41801141
Right Ok, this is a weird one and I am stumped.

In file explorer change the extension of the file from .xlsx to .zip

Double click on the file and Explorer will then expand the contents of the zip file.

Browse to:


For me it opened in IE and you will find the following:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
- <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
  <sheetPr codeName="Sheet5" /> 
  <dimension ref="A1:FR1" /> 
- <sheetViews>
  <sheetView tabSelected="1" workbookViewId="0" /> 
  <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25" /> 
- <cols>
  <col min="1" max="1" width="10.85546875" customWidth="1" collapsed="1" /> 
  <col min="176" max="16384" width="9.140625" collapsed="1" /> 
- <sheetData>
- <row r="1" spans="1:1" ht="16.5" customHeight="1" x14ac:dyDescent="0.25">
- <c r="A1" t="s">
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> 
  <pageSetup orientation="portrait" /> 

Open in new window

As you can see in line 4, the dimension is set for A1:FS1. I tried editing the xml file but didn't have any means of doing so, IE gives option to open in Word but then Word said all xml content would be removed.

How you would allow for this in an all encompassing file cleaner, I don't know.

Rob H

Author Comment

ID: 41801845
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)

Author Comment

ID: 41803872
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.
LVL 33

Expert Comment

by:Rob Henson
ID: 41803919
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.

Author Comment

ID: 41804037
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.

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question