Debloating Excel 365 workbook - I recommend this solution

[JeffGrant]

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.

Resetting the Last Cell in an Excel Worksheet

By David Ringstrom, CPA It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data. Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000. In programming parlance, this is known as the...

www.davidringstrom.com

 

[6StringJazzer]

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.

 

[JeffGrant]

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.

 

[6StringJazzer]

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.

 

[Alex Blakenburg]

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: