Small File, Low Performance

[DBusse706]

Hello,

I have an Excel file that is larger in size than it 'should' be. The file is a simple template that is placed on a network drive and updated by between 2-3 dozen people on a daily basis. It's 14 sheets, but should only have about 20 rows and 10 columns of data per sheet. When the template is created in a new workbook the file size should is about 50kb. However, after a couple of weeks of use, the actual template is about 1.5mb (which still isn't huge). This 1.5mb handles worse than 100mb+ files I've used. Changing from sheet to sheet takes several seconds, Excel hangs and sometimes crashes, and copying cells from this sheet can get kind of weird.

• The problem is not the used range. I have already checked by deleting all rows/columns outside of the range that is used and the filesize/performance isn't getting better.
• There are no calculations in the sheet, it is purely holding text data.
• It is not formats (I have tried removing all formatting from the cells to see if that would help. It hasn't).
• There is no VBA in the book
• It is not a Shared Workbook
• Previous Versions is not turned on

When I open this 1.5mb file, the Task Manager says Excel is using 200-500 MB of memory. Again, we're talking about a total of about 3,000 cells with information (~200 per sheet), which should be nothing! Does anyone know of a data leak that might be occuring and a possible fix? I assume it has something to do with the number of different users accessing the same workbook. I'm hoping for a better solution than to replace the workbook every month or so.

Thanks!

 

[dave3009]

Hi and welcome to the board.

Did you try recreating the workbook, that is when it's not in use, open it->copy the data from the pages to a new workbook->save the new workbook->save it to it's desired location?

Sometimes files can pick up odd little bugs and having experience similar before it could be a solution for you.

HTH

Dave

 

[MARK858]

I know you stated you have checked the usedrange but can you run the macro below just to double check as you have stated it is all text it shouldn't do no harm.

Sub LoseThatWeight()

    Dim x As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

    For x = 1 To Sheets.Count
        With Sheets(x)
            LastRow = .Cells.Find(what:="*", after:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            LastCol = .Cells.Find(what:="*", after:=.Range("A1"), LookIn:=xlValues, _
                                  lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[DBusse706]

Thank you for the responses. I'll try recreating the workbook. I tried this once before and the file got out of hand again within a month. Hopfully it was just a weird accident and maybe there was something wrong with a handful of cells that were copied to the new workbook (though I'm not sure what that would be). I'm starting completely fresh this time and hopefully that will fix/prevent the issue.