Excel file size unexpectedly increased when columns were deleted

au2010

New Member
Joined
Jun 7, 2012
Messages
37
Hello.

In an .xlsb workbook, I have a tab with 63k rows and 68 columns.

Of the 68 columns, 17 are data columns (hard-coded numbers or text) and the other 51 are formulas. The formulas are either simple "IF" statements (eg, if Column X is greater than 1, "Yes, No") or vlookups referring to very small ranges in other tabs in the workbook. None of the formulas are SUMIF or COUNTIF or anything like that.

At some point, I decided that 9 of the 51 formula columns were no longer needed, so I deleted those columns (no other cells anywhere in the workbook referred to those 9 columns). I did not change anything else anywhere in the file.

But when I saved the file, I noticed that the file size unexpectedly INCREASED from 31MB to 34MB.

So I reverted to a saved backup version, and this time I just deleted the formulas from those 9 columns, but I did not delete the columns themselves (in other words, the tab still has 68 columns, but now 9 of them have nothing but empty cells).

In this case, the file size DROPPED from 31MB to 28MB.

Anything ideas on what is causing this behavior?

Version: Excel 2016 64-bit, Microsoft Office Professional Plus 2016

Thank you.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Not certain what is happening with your project but I have run across similar situations on occasion here. For some reason
Excel will decide to add some unseen data to the file. I don't know where that data is or what it is comprised of but as you
have found, it adds to the overall size of the workbook.

Here is a macro to search out and rid the workbook of that 'extra data'. If it is there, the macro deletes it without affecting
the remainder of your project. HOWEVER, I caution you to first try it on a COPY OF YOUR WORKBOOK. If it works then you
can apply it as you decide.

VBA Code:
Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub
 
Upvote 0
Thank you for the suggestion. Unfortunately, I'm basically a novice/entry-level Excel user, and I'm not familiar with how to do macros or VBA code, so I won't be able to test it out. But I appreciate your taking the time to answer my question.
 
Upvote 0
Left click on a sheet tab.
Select "View Code".
Click on INSERT from top menu.
Select MODULE.
You will see that MODULE is added to the list of items in the left side window.
In the large white window on the right hand side, paste the code I gave you in my last post.

VBA Code:
Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

Click on the large X in the upper right corner. This takes you back to the sheet view.
Click on the DEVELOPER TAB, then INSERT, then select the small button image from the few icons presented.
Select a location on the sheet and left click. The button should now be pasted to the sheet.
Another small window opens with a list of the macros you have in your workbook. Select the macro
"LipoSuction", then click OK.
That macro is now assigned to the command button. When you left click the button the macro will run.

IF YOU DO NOT HAVE THE DEVELOPER TAB SHOWING IN YOUR MENU BAR ... open the Excel Options menu.
In the POPULAR selection there is a selection for 'Show the Developer Tab In The Menu Bar'. Click that,
then click OK. Go back to the command button and left click it. The macro will run and remove any
unnecessary data.
 
Upvote 0
Wow, thank you for the detailed instructions. I will try this as soon as I have a chance and then reply here. Thank you again!
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

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