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.
LVL 5
rberkeConsultantAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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:

sheet-trim-bug-4.zip\xl\worksheets\sheet1.xml

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" /> 
  </sheetViews>
  <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" /> 
  </cols>
- <sheetData>
- <row r="1" spans="1:1" ht="16.5" customHeight="1" x14ac:dyDescent="0.25">
- <c r="A1" t="s">
  <v>0</v> 
  </c>
  </row>
  </sheetData>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> 
  <pageSetup orientation="portrait" /> 
  </worksheet>

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.

Thanks
Rob H
0
 
Wayne Taylor (webtubbs)Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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.

Thanks
Rob
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rberkeConsultantAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
To get a true answer to your question, you need to upload the workbook.....
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
rberkeConsultantAuthor Commented:
Geez !!  I thought I had uploaded it.  My sincere apologies.
And, yes I always saved the file, closed and reopened the file.
sheet-trim-bug-4.XLSX
0
 
Rob HensonFinance AnalystCommented:
No worries, I wasn't having a moan; just going along with your triste.
0
 
rberkeConsultantAuthor Commented:
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)
0
 
rberkeConsultantAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
rberkeConsultantAuthor Commented:
YOU ARE CORRECT !
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.