4

I have an Excel file that is larger than 7MB. To understand where the weight is coming from, I've used the method described in this SU question.

I narrowed most of the weight down to one sheet, and did ctrl+End to check what is the last cell in that sheet. Oddly enough, that cell was in the last possible row in the sheet (i.e. row 1048576).

I then proceeded to clear the formatting from all of the unused rows, and then - just in case - selected all the blank rows (beneath my data) using alt+Shift+ and alt+Shift+, then right-click and delete.

However, after saving the file, closing it, and opening it up again - pressing ctrl+End still throws me to the last cell.

I've read up on the VBA equivalent of doing this manually, and it seems that this SO Question refers to the same thing - the Worksheet.UsedRange property.

Before I dive into VBA - is there something I can do manually to fix this?

P.S. Before marking this as a duplicate, note I've intentionally exhausted other options in my search for an answer.

EDIT:

To clarify, I've seen this answer (and tried it) and this KB article (and tried it as well) - no cigar. Same with this more recent answer.

4
  • 1
    Just for confirmation: did you tried keeping only the problematic worksheet in your file and it's really the one responsible for the size? Does deleting empty rows and columns and saving doesn't decrease file size? Do you have any pictures / attachments in your file? I know most of these are listed in your links, but it always helps to list the exact steps you've tried. Aug 16 '18 at 11:32
  • Answering all your questions at the order of asking: That sheet is indeed the culprit, deleting empty rows & cols doesn't seem to have an effect on the file size, no pics/attachments or any data feeds into the workbook. Thanks for playing! :)
    – Tom Granot
    Aug 16 '18 at 11:38
  • Any VBA in the workbook that might run on opening ? Aug 16 '18 at 12:03
  • @PatrickHonorez not that I can tell.
    – Tom Granot
    Aug 16 '18 at 13:48
3

I appreciate the the extra effort you've taken to document your efforts. Thank you.

I frequently reset bloated books using the methods that don't work for you.  There are only two differences I can identify:

The first is that I've never tried to right-click -> delete. I'm a keyboard guy so I've always used Alt+E+D. That shouldn't matter one bit, but stranger things have happened.

The second difference is that I've never been stuck like you, it always resets after saving. Big help there, right?

A few possible solutions follow:

Horizontal/Vertical Bloats

Today I had an issue where my VBA merged sheets vertically and my worksheet inexplicably bloated horizontally. I was initially confused and concerned because I've only had vertical bloat and didn't think to check horizontally, until my reset attempt failed twice. Please humor me for the sake of completeness and to keep that from happening to you:

  1. Begin by closing any other Excel sessions, so the current workbook is the only workbook open.
  2. Go to the of your data on the right side and select the first empty column - the whole column.
  3. Ctrl+Shift+.
  4. Alt+E, then D.
  5. Ctrl+Shift+End.
  6. Alt+E, then D.
  7. Ctrl+Home
  8. Scroll to the bottom of your data
  9. Select the first empty row - the whole row.
  10. Ctrl+Shift+.
  11. Alt+E, then D.
  12. Ctrl+Shift+End.
  13. Alt+E, then D.
  14. Ctrl+Home
  15. Ctrl+S

Yes, the two Ctrl+Shift+End lines are redundant, but I think your can survive the 15 keystrokes.

You "shouldn't" need to close your workbook after saving it, but I won't advise against it.  Give it a shot. If that doesn't work, well - try VBA real quick.

The VBA Way

Don't be afraid of the big bad VBA, it's a great tool and what we're doing well only take a few seconds.

  1. Make sure the suspected worksheet is open and active.
  2. Alt+F11 to open the editor.
  3. Ctrl+G to open the immediate window.
  4. Type ?activesheet.usedrange.address and press Enter.
  5. If the range looks normal (not excessively large) then the problem is not your range.
  6. If the range is excessively large. then type activesheet.usedrange and press Enter. Note the first command used a ? and this doesn't.  That's because the question mark is used when we want VBA to display text, so we can see a value or status. If we don't use a question mark, then we're telling VBA to perform an action.
  7. Once you've pressed Enter, put your cursor on the line where you used the ? and press Enter.
  8. if the range changed then you're done. If it's the same, then you still have a problem.

Very Hidden Worksheets

The final task is to look for hidden worksheets. Not just hidden, but very hidden (I'm not making this up).

  1. The project explorer should already be open on the left side of the screen. Press Ctrl+R if it isn't.
  2. The window has minor differences between Excel versions, but they all share the same expanding/collapsing tree behavior. 
  3. The number of projects displayed depends on the number of open Excel workbooks and addins. If you closed all other workbooks and there are more than a few projects showing, then you've got something else going on that we can cover in detail later.
  4. For now, find your project and expand the folders to expose the workbook objects (the individual worksheets and the ThisWorkbook object). You'll recognize them because they will have your worksheet names in brackets.
  5. Once found, look for a name that isn't listed in your workbook with a worksheet tab.
  6. if you find one, select it in the explorer window and go to the properties window (below the explorer or F4).
  7. Scroll down to find visible, it should be one of two values: xlhidden or xlveryhidden.
  8. Click inside the value to open a drop-down menu, and select xlvisible.
  9. If it is neither of these, then you've selected the wrong sheet and possibly wrong project, - double check your selections. 
  10. If you found and un-hid the sheets, then go back to every one of them and reset those ranges to see if your book shrunk (don't forget to re-hide the sheets.

FYI: A very hidden sheet is not listed in the hide/unhide context menu in Excel, so it's one of the better ways way to protect configurations and helper columns.

  1. We're done with VBA, so close the editor and go back to Excel. Press Ctrl+F3 to open the name manager.
  2. Look for unexpected named ranges. I generally don't recommend deleting named ranges unless you are very familiar with your workbook, because a lot of productive Excel/VBA  solutions will not function properly if you remove a range they need; however, if you find a range with errors, pointing to non-existent workbooks, referencing unused ranges, or with some other blatantly wrong value - then they are ripe for removal.

Some Other Helpful Advice

This is significantly longer than I anticipated so I'll try to wrap it up ...

  • Check your Conditional formatting - just like named ranges, you're looking for blatantly wrong or reproduced conditions.

  • Check your queries and data models.

  • Verify you don't have pictures/charts/listobjects in a hard to find location (the check we did with name manager should catch most, but not all, of them).

  • Verify you don't have custom configuration settings out of whack (I noticed a colleague's style gallery on the home tab was populated with the same 20% accent, and expanding it revealed hundreds. Using the VBA editor, I typed ?activeworkbook.styles.count and it returned more than 19,000; for comparison, mine had 47).

  • Watch for excessive formatting - a few hundred thousand rows of numbers formatted as currency is not the same as a few hundred thousand rows formatted with cell borders patterned like a maze filed with different colors and many different custom number formats, symbols, and fonts.

  • Check the data itself, a few hundred rows spanning 20 columns with every cell filled to the limit would be an embarrassing - but possible - cause of bloat

  • And lastly, the nuclear option. Save each sheet separately as a CSV text file and import it into a new workbook... this thing is guaranteed to work unless you have corrupt data - but rebuilding your formats, connections, and bla bla bla could be a pain.

4
  • 1
    sorry about the mess. input device was an s7. started the answer yesterday and tried to make coherent statements, or at the very least, use intelligible grammar... it was a losing battle Aug 16 '18 at 12:43
  • 2
    Thanks for al the help on my FIRST post. Now I have the realistic expectation that a team of editors will follow me through every thread ensuring commas are properly beneath periods in a semicolon and every inflection is italicized for emphasis. Aug 16 '18 at 13:21
  • 1
    @t0mgs Btw, are you aware that clicking the "edited …" wording above the user info for the last editor will lead to the revision history? -§- ProfoundlyOblivious, lol. Not likely! PS Do let us know if anything here actually worked.
    – robinCTS
    Aug 16 '18 at 13:24
  • Super updated and helpful answer @ProfoundlyOblivious and @robinCTS! Thank you!
    – Tom Granot
    Dec 11 '19 at 16:17
3

I just spent a few hours chasing this down. Every one of the manual tricks - selecting, deleting, using the "Clear Formatting" menu command, deleting rows, using VBA to delete rows, telling VBA to recalculate UsedRange all failed. The file would not get smaller and the UsedRange was always the max (million) rows.

The key -- when you print the UsedRange.Address in VBA, there are no row numbers!

? activesheet.usedrange.address
$A:$AH

So there is no subset of the rows specified in UsedRange. Something is forcing the range to the entire sheet. In my case, that something was RowHeight.

This worked for me. Open VBA (Alt-F11) and use the Immediate mode window. Enter these one at a time:

? activesheet.name
[shows sheet name]
? activesheet.usedrange.address
$A:$AH
activesheet.usedrange.rowheight = 23
? activesheet.usedrange.address
$A$11:$AH$1493

Not only does universally changing the rowheight allow the UsedRange to be smaller, it automatically recalculated UsedRange for me as part of the change!

Bingo, the file is now 2.5MB smaller.

0

I was asked to look at an Excel workbook that was bloated to 60 mb and was prone to crash. I determined that lots of the sheets had formula and/or formatting that went down to row 1048576. I determined 15,000 rows would meet their need. So at row 15,001, I did a Ctrl+Shift+End or Ctrl+Shift+Down Arrow. On the highlighted cells, right click and Click Deleted, Select Entire Row. I changed all of the ranges in the formulas from A:Z to be $A1:$Z$15000. Saving the file removed the excess rows from all but one sheet. The workbook is now down to 10 mb. I tried several options to remove the excess rows, but nothing worked. I read a post to highlight the Range and change the row height. So, I selected a blank row and changed the row height. I deleted the entire rows again and saved the spreadsheet. This time the Last row is now 15000. The file size is now 3.6 mb. Hope this helps.

0

What solved this problem for me was the following (found this solution somewhere on an Excel forum I do not remember):

  1. Open a blank sheet and see what the default row height is.
  2. Go back to the problematic sheet and select all rows that you have tried deleting (but still appear after being "deleted"). Once selected set the height to the default number seen on step 1.
  3. Select all rows again and delete them, as it was mentioned by the OP. Now, these unused rows should disappear and the file size should be reduced significantly.
0

Another way would be to just select the range you need and copy it to a new worksheet.

0
0

I had tried lots of the suggested techniques and finally this worked for me... a hash of bits from various stages/suggestions

Stick the VBA launcher on your quick access toolbar On the offending tab ... launch VBA ... view 'immediate' sheet Type in ?activesheet.usedrange.address (enter) For me this was a full column range ... so the problem lay there but deleting rows was not resolving the issue ...

... I established the default row height for the file (i.e. checked with a new tab - 12.75) ... I highlighted the rows to clear from 'usedrange' (use the shift ctrl keys etc for speed) ... checked row height ... it was 12.75 (so in theory not a problem) ... amended row height (I used 13.00) ... then amended row height back to 12.75 ... then deleted the rows

(have to admit I haven't tried without amending the row height back to 12.75 but it's only one step and the rows are already highlighted)

back into VBA ... ?activesheet.usedrange.address range was now reset (specific area not full columns)

Wiping about 3MB off the file size with each tab being fixed!!!

(Excel 2016)

Thanks to everyone for their skills and suggestions Soooooo pleased to have finally found a fix

1
  • actually ... just amending the rows to a new height and then deleting works for me. I also needed to do the used range address check in VBA to force excel to complete the reset Feb 25 '20 at 11:10
0

Try setting the row heights to system defaults (might differ from system to system).
Open new workbook, check row height and apply to the erogenous worksheet.
This helped me, I found it here.

-1

save/close/open should reset the UsedRange. Clear formatting is not enough. You should Clear, or better, Delete he rows & cols past your expected last cell.

For your size problem, Excel 2013 has an interesting add-in called Inquire, which allows you to analyze your wb, and also has a 'Clean excess cell formatting' option

2
  • 1
    I have deleted the rows... it's listed in the question. I'll look into inquire tho
    – Tom Granot
    Aug 16 '18 at 8:57
  • 1
    Patrick - Inquire does not work in this case. It clears the formatting, but the problem persists.
    – Tom Granot
    Aug 16 '18 at 10:42
-2

In the past, the way to work around this problem is that immediately after deleting the blank rows or columns, you would have to save. Then Excel would recognize the new end to the data. With Office 365, it's trickier because you can't manually save. But I have turned off autosave, reset the end, and then turned autosave back on and that has worked.

1
  • 1
    What do you mean you cannot manually save? Why do you believe the use of Office 365 makes a difference?
    – Ramhound
    Dec 11 '19 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.