Solved

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

Posted on 2016-09-14
12
43 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 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
To get a true answer to your question, you need to upload the workbook.....
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

Expert Comment

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

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now