Excel increasing it's size

[KUYJS]

Hi,

I have a shared workbook in a shared drive with 12 sheets in it. 28 users work on it in same or different sheets at a time.

I need to track work on sheet name "imp" on realtime basis. So i created vba to save workbook if any cell in status column in the sheet name "imp" gets updated. This is working fine.

But file is increasing it's size dramatically frpm 2mb to 50/6mb, whereas data is of between 2 to 3mb only. Due to this i have to ask all users to close file so that i cteate new file for them to work. This happens multiple time each day.

May i get any help on this

 

[Kamolga]

Hi,

If you delete all empty columns to the right and lines down on your sheets, does the size come back to normal? That would mean Excel saves empty values or normal format on every cell, like with cells.copy and paste
Can you post your codes to maybe find another possibility?

 

[Michael M]

You could also use this code on your workbook.
It reduces bloat on ALL the sheets

Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

 

[Kamolga]

You could also use this code on your workbook.
It reduces bloat on ALL the sheets

Public Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Activate
    ActiveSheet.UsedRange
Next ws
End Sub

I used to have the maccro below in my taskbar (my first loop I believe), which would delete from first empty row and column (not clean)

Sub MacSizeReduction()
' Reduce worksheet size (MB) by deleting empty rows&columns (such as formated cells) -> top down & right
'! Works on active sheet
'! Will delete borders, objects and formating (also conditional) of last columns & row if no alphanumeric data (put 0 in white to avoid)
    ActiveWorkbook.ActiveSheet.Cells.Select
                                                                        'Delete empty last rows
        Dim i As Long
            i = 1
            
        Do While Application.WorksheetFunction.CountA(Selection) <> 0
        
                Rows(i).EntireRow.Select
                Range(Selection, Selection.End(xlDown)).Select
            
                i = i + 1
        Loop
        
        Selection.Delete
    ActiveWorkbook.ActiveSheet.Cells.Select
                                                                        'Delete empty last columns
        Dim k As Long
            k = 1
            
        Do While Application.WorksheetFunction.CountA(Selection) <> 0
            
                Columns(k).EntireColumn.Select
                Range(Selection, Selection.End(xlToRight)).Select
                
                k = k + 1
        Loop
        
        Selection.Delete
                    
    Range("A1").Select
End Sub

I used to have the issue, especially with imports. Working now with tables and therefore avoiding copy-paste of full rows and columns helped to keep that under control. I have experienced some good vba code which reduced significantly the size of a file (from 25MB to less than 1), replacing conditional formatting on big ranges or formulas and pivots by values.​