File size grows after running macro

I have a macro that cuts columns and inserts them into a new position in a sheet, and does so on two different sheets. The file before is 562KB. Once I run the macro and save, the file size jumps to 749KB. If I then close the file, reopen, and save, the file size goes back to 562KB. I'm looking for ideas as to why this is happening. Thanks!

Re: File size change after running macro

Thanks for the reply. I did suspect this, so I created a KillVar sub that does a 'SET <object> = nothing' for each of the objects named in the cut/paste subs, but it doesn't seem to affect the file size. If the file size changes once the file is closed/reopened/saved, this seems to suggest that something is being stored at the first file save after running the macros. Is there a way to check 'memory' to see if something is still being stored? (not sure I am using the correct lingo, I am somewhat new to VBA). What would be stored in the file itself, and then cleared once the file is reopened?
My KillVar sub for reference, in case I'm not executing this properly.

Sub KillVar()
'
' Sets all variables to nothing so file size is saved


Set BT_Asm_Specific_Cells_Start_Column = Nothing
Set BT_Asm_Specific_Columns_Range = Nothing
Set BT_Current_View_Cell = Nothing
Set BT_Date_Cell = Nothing
Set BT_Quote_Specific_Cells_Start_Column = Nothing
Set BT_Quote_Specific_Columns_Range = Nothing
Set BT_Notes_Range = Nothing
Set ECB_Asm_Specific_Cells_Range = Nothing
Set ECB_Asm_Specific_Cells_Start_Column = Nothing
Set ECB_Current_View_Cell = Nothing
Set ECB_Quote_Specific_Cells_Range = Nothing
Set ECB_Quote_Specific_Cells_Start_Column = Nothing
Set MsgBox_to_Asm_string = Nothing
Set mycell = Nothing
Set myRng = Nothing
Set lArea = Nothing
Set ScreenUpdatingStatus = Nothing
Set ECB_First_Row_Range = Nothing
Set BT_Max_Qty_Rows = Nothing
Set ECB_Quote_Specific_Cells_Range = Nothing
Set ECB_Asm_Specific_Cells_Range = Nothing
Set BLC_First_Row_Range = Nothing
Set BLC_BOM_Data_Paste_Range = Nothing
Set BLC_Notes_Data_Paste_Range = Nothing
Set BLC_Board_Qty_Cell = Nothing
Set BT_Max_Qty_Rows = Nothing
Set i = Nothing
Set j = Nothing
Set k = Nothing


Application.CutCopyMode = False


End Sub

Re: File size change after running macro

Quote from Dave Hawley;509376

Also See: Excel File Size Increase

Thanks Dave. I have gone through this page previously, but it doesn't affect this specific case. If I run the macro and save, none of these suggestions affect the file size when I save again. Only when I close, reopen, and save does the file size go back to the original size.

Re: File size change after running macro

Quote from SMChacko;509379

Normally, one would want to destroy the variables based on their type

Set Object = Nothing
Integer = Empty
String = vbNullString
Erase Array

From what you mentioned, I don't pressume to say this would be a solution, but try anyway, just to be sure.

I have updated the file per your suggestion, but no change in behavior.

Sub KillVar()
'
' Sets all variables to nothing so file size is saved


'Variables set as Range 'Set = Nothing'
Set BT_Asm_Specific_Cells_Start_Column = Nothing
Set BT_Asm_Specific_Columns_Range = Nothing
Set BT_Current_View_Cell = Nothing
Set BT_Date_Cell = Nothing
Set BT_Quote_Specific_Cells_Start_Column = Nothing
Set BT_Quote_Specific_Columns_Range = Nothing
Set BT_Notes_Range = Nothing
Set ECB_Asm_Specific_Cells_Range = Nothing
Set ECB_Asm_Specific_Cells_Start_Column = Nothing
Set ECB_Current_View_Cell = Nothing
Set ECB_Quote_Specific_Cells_Range = Nothing
Set ECB_Quote_Specific_Cells_Start_Column = Nothing
Set mycell = Nothing
Set myRng = Nothing
Set ECB_First_Row_Range = Nothing
Set ECB_Quote_Specific_Cells_Range = Nothing
Set ECB_Asm_Specific_Cells_Range = Nothing
Set BLC_First_Row_Range = Nothing
Set BLC_BOM_Data_Paste_Range = Nothing
Set BLC_Notes_Data_Paste_Range = Nothing
Set BLC_Board_Qty_Cell = Nothing


'Variables set as Boolean '= Nothing'
Set ScreenUpdatingStatus = Nothing


'Variables set as Long '= Empty'
lArea = Empty


'Variables set as Integer '= Empty'
i = Empty
j = Empty
k = Empty
BT_Max_Qty_Rows = Empty


'Variables set as String '= vbNullString'
MsgBox_to_Asm_string = vbNullString


Application.CutCopyMode = False


End Sub

Re: File size change after running macro

Quote from snb;509412

The size of your file is highly dependent on the usedranges in the sheets (especially when formatted).
Declare as little variables as possible, most of the variables do not have to be public. and Object variables can be easily avoided using With...End With.
As MS isn't the most comunicative business most of their programs have to be 'discovered' The logic behind their choices ramains most of the time in darkness. So many 'why' question remain unanswered.
To minimize uour files: reduce the usedranges of the worksheets as much as possible.

Thanks snb for replying. For reference, I have 35 macros in this workbook, and zero (0) public variables. Most are procedure-level, some are module level, but none are public (Mostly because I wasn't sure of the proper way to use then and clear them). In the two macros that are unders suspect, I'm using zero public variables. According to this page: http://www.ozgrid.com/VBA/variable-scope-lifetime.htm, all the variables therefore should be getting destroyed when the macros are completed. Am I understanding this? Again, I run the macro (do NOTHING else), save, the file size increases by ~200kb. I close the file, reopen, and save again (i.e. I do NOTHING else), and the file size returns to original, lighter size. Again, the question remains...what is being stored that gets destroyed at file close/reopen? And again, this is related to a macro that cuts entire columns, and inserts them into another location in the same worksheet

Side question #1: the above mentioned OzGrid page mentions module level variables are destroyed when the workbook closes or the end statement is used. Does "End Sub" satisfy this requirement? (Note that this does not apply to my 2 macros under suspect, but I am curious) If so, it would seem that the KillVar sub I have is pointless in my workbook, since I have no public variables.

Side question #2: Would using a KillVar sub as I've listed above to kill any public variables be a best practice? Then call this sub at the end of every other sub in the workbook?