How to determine what uses workbook memory in excel
Posted by Duncan on December 18, 2001 2:17 AM
I have a workbook which is 30Mb which I would like to reduce.
How can I find out which sheets/macros/formulae are taking up the memory so that I can reduce the size?
Thanks
Duncan
Posted by Mark O'Brien on December 18, 2001 9:24 AM
There are ways to sort of calculate what takes what space. However, it's easier to live by rules of thumb. The main rule is that Formulae take up for space than just a simple value in a cell. If a formula is no longer needed (e.g. the data that it's linked to isn't going to change) then you could free up some space by selecting the cells with the formulae and doing a Copy|Paste Special with values.
Macros can also take up more space. I've written some pretty hefty VBA but never really went over a 1MB file. I wouldn't be too concerned with the macros, however, one trick that I have used is to put all of the macros in a separate workbook from the data workbook and then open up both files when you want to work on them. The advantage here is not processor time (since you may take up more processing time) but you do cut down on the size of your data file.
In general, if a sheet has a lot of data or code and formulas on it/in it, then it's going to take up a lot of space.
If you need any help, then just repost.
HTH
Posted by Mark O'Brien on December 18, 2001 9:37 AM
There are ways to sort of calculate what takes what space. However, it's easier to live by rules of thumb. The main rule is that Formulae take up for space than just a simple value in a cell. If a formula is no longer needed (e.g. the data that it's linked to isn't going to change) then you could free up some space by selecting the cells with the formulae and doing a Copy|Paste Special with values.
Macros can also take up more space. I've written some pretty hefty VBA but never really went over a 1MB file. I wouldn't be too concerned with the macros, however, one trick that I have used is to put all of the macros in a separate workbook from the data workbook and then open up both files when you want to work on them. The advantage here is not processor time (since you may take up more processing time) but you do cut down on the size of your data file.
In general, if a sheet has a lot of data or code and formulas on it/in it, then it's going to take up a lot of space.
If you need any help, then just repost.
HTH
