1

I designed a tool in Excel that contains a sheet that acts as a user input form.

On the sheet is an active-x combo box, eight shapes with assigned macros, and a range formatted with colour and borders to display data.

This sheet takes up around 3Mb of memory. I made a copy and after deleting this sheet the file size drops to around 200Kb and everything is much smoother.

So far I have tried:

  • Unhiding all rows/columns and filling all cells in with "No fill"
  • Eliminating excessive formatting tool (Inquire tab on the ribbon)
  • Deleting all shapes/combo box one by one
  • Removing borders and all text
  • 'Demerging' all merged cells
  • Removing unused styles
  • Removing conditional formatting

I was left with a completely blank sheet, and still after deleting the sheet the file size dropped by 3MB.

Why might this be happening?

Community
  • 1
  • 1
James Baker
  • 331
  • 3
  • 8
  • 26

1 Answers1

1
  1. Identify the last filled up row of data and click on any cell in one row below that
  2. Select from that row till the end and delete all rows (via right-click menu)
  3. Do the same for columns
  4. Save and close the file
  5. Shut down MS Excel
  6. Reopen the file

Let me know if it helps.

daZza
  • 1,669
  • 1
  • 29
  • 51
  • That's a good idea hadn't thought of that. I've just tried to delete all columns and I got a "There isn't enough memory to complete this action" error. I tried again with just one column and the same thing happened. Confusing seeing as the columns are all blank. – James Baker Aug 18 '16 at 10:01
  • How much RAM do you have? How much of it is available (check in task manager)? 64bit-Office? In case your RAM is mostly full, you could try increasing virtual memory: http://www.techulator.com/resources/2430-Increase-your-Virtual-Memory-size.aspx Also is the file stored on a network drive? – daZza Aug 18 '16 at 10:05
  • I have 8Gb of Ram, 46% of it is currently being used up and around 155Mb by Excel itself. I am using 64-bit Office. I'll look into increasing virtual memory now. The file is stored on dropbox, could that be the issue? – James Baker Aug 18 '16 at 10:10
  • Dropbox files are stored locally on your PC afaik, so that shouldn't be the issue. You could also try cleaning up your temp folder (type %TEMP% into the Windows Explorer adress bar) – daZza Aug 18 '16 at 10:13
  • Yeah I have dropbox installed so it's all local. I've done a disk cleanup and now excel won't open anything - "There was a problem while sending the command to the program". I'll try and increase the virtual memory now and see where that gets me. Will get back, thanks for the advice. – James Baker Aug 18 '16 at 10:24
  • After disabling all add-ins and re-installing Office I managed to get it open again. I've tried deleting all blank rows and columns by right-clicking but still no joy I'm afraid. – James Baker Aug 18 '16 at 11:17