[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-09-14
12
Medium Priority
?
67 Views
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.
0
Comment
Question by:rberke
  • 6
  • 5
12 Comments
 
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.
0
 
LVL 34

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.

Thanks
Rob
0
 
LVL 5

Author Comment

by:rberke
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.
0
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 34

Expert Comment

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

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.
0
 
LVL 5

Author Comment

by:rberke
ID: 41800170
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
 
LVL 34

Expert Comment

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

Accepted Solution

by:
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:

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
 
LVL 5

Author Comment

by:rberke
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)
0
 
LVL 5

Author Comment

by:rberke
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.
0
 
LVL 34

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.
0
 
LVL 5

Author Comment

by:rberke
ID: 41804037
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

873 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