Debloating Excel 365 workbook - I recommend this solution

JeffGrant

Well-known Member
Joined
Apr 7, 2021
Messages
519
Office Version
  1. 365
Platform
  1. Windows
HI all,
Excel in Office 365 really does not like being debloated, regardless of how many times we delete rows and columns either manually or with VBA.

However, this linked works a treat. doing this manually reduced my workbook by 10Mb.

 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
From your link:

As with many aspects in Excel, the used range is fraught with nuance, but you can easily set up an example to understand the concept. As illustrated in Figure 1, carry out these steps in a blank worksheet:
  • Type the number 100 in cell A10.
  • Press Ctrl-Home to move your cursor back to cell A1.
  • Press End-Home to move to the last used cell in your worksheet. Your cursor should now return to cell A10.
  • Press Ctrl-G (or the F5 key) to launch the Go To dialog box, enter the address TX5000, and then press Enter.
  • Type the number 100 in cell TX5000 and then press Enter.
  • Move your cursor up one cell and then press the Delete key to clear cell TX5000.
  • Press Ctrl-Home to move your cursor back to cell A1.
  • Press End-Home to move to the last used cell in your worksheet. Your cursor will now return to cell TX5000, even though you erased it.

No amount of deleting rows or columns will reset this used range of your worksheet, but you can use a one-line macro to resolve the issue:

This is not correct. They gave up too soon. Deleting the data won't help but deleting the rows and columns solves it. Next do this:
  1. Put your cursor in B11
  2. Hit CTRL+SHIFT+END
  3. Right click, Delete, Entire Row
  4. Put your cursor in B11
  5. Hit CTRL+SHIFT+END
  6. Right click, Delete, Entire Column
  7. Save and close workbook
  8. Re-open it
  9. Press CTRL+END to go to the last cell in the sheet
  10. Your cursor will now be at A10.

No messing with VBA required.
 
Upvote 0
Thank you 6StringJazzer for your reply.

Why not look at this solution as an alternative. What you have described above I have done many times without success. I have several VBA routines that do as you have described. My model has 73 sheets in it. VBA is required to debloat the worksheets otherwise I would be drinking a lot more coffee than I o now.

Regardless of the size of my model, the solution provided in the link worked straight up and reduced the bloating by 10Mb. That is why I shared it here.
 
Upvote 0
I'm sure that the VBA solution works, I am not questioning that. I have never had a case where I had multiple worksheets with this issue and needed to use VBA to resolve it, but it makes perfect sense. But you have to have at least some tiny comfort level with VBA.

The method I described has always worked for me; this is the first time I've heard from someone where it failed.
 
Upvote 0
Thank you 6StringJazzer for your reply.

Why not look at this solution as an alternative. What you have described above I have done many times without success. I have several VBA routines that do as you have described. My model has 73 sheets in it. VBA is required to debloat the worksheets otherwise I would be drinking a lot more coffee than I o now.

Regardless of the size of my model, the solution provided in the link worked straight up and reduced the bloating by 10Mb. That is why I shared it here.
If you found that worked on 73 sheets we would need more detail on what you actually did.

The only thing done in that Article seems to be the suggestion to use the line "ActiveSheet.UsedRange".
This does not clear the underlying cause of the bloat it only tells Excel that it should recalculate the used range.

The other way to get it to recaculate the used range is to click on the Save button. (you don't need to close and reopen, just clicking on Save is enough to get it to recalculate).
Both of these will do nothing unless you have deleted the rows and columns that are causing the Used Range to think it needs to be bigger than what the Dataset indicates it should be.
eg in his example if he didn't delete the content of TX5000 then Used Range would have no reason to recalculate.

If you are having issues with Bloat and you are not using a macro to clean up your worksheets then since you have MS365, try opening your workbook in Excel Online (ie Excel on the Web) and use the relatively new Check Performance feature (Review > Check Performance).
It might be available on the Beta channel of Excel but I don't have it

See 2.5 min video below:
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top