Small File, Low Performance

DBusse706

New Member
Joined
Nov 11, 2015
Messages
2
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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.

Rich (BB code):
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

New Member
Joined
Nov 11, 2015
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,166
Messages
5,623,119
Members
415,956
Latest member
Footballtend

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
Top