Need to find a way to shrink a workbook

[Abz_Tahar]

Hi I have a workbook that is over 100 tabs currently coming in a 23MB. Im afraid that when people start to populate the workbook even more, it will become even bigger. What can I do to shrink the workbook size without obviously having to delete tabs?

[Pete_UK]

On each tab you can do CTRL-End - this should take you to the last used cell. If it is much larger than you expect, then you can delete the empty rows and columns and then save the workbook, close and re-open it. Then once your users start adding data, at least you do not have inflated used-ranges.

You can also check that there are no unwanted shapes in the workbook, and also check there are no unnecessary named ranges. Also, conditional formatting can hog memory, so check you do not have any unwanted CFs.

Hope this helps.

Pete

[Abz_Tahar]

What about using VBA? are there any ways

[Glenn Kennedy]

No need - Pete's suggestion is spot on. Here's a quick wqay to achieve the desired end:

First, move to the last cell that contains data and select a cell in the next row.

Press Ctrl + Shift + Down arrow key. Right click in the selected area and click delete. From the window opened, select delete entire row.



Next:

Move to the last cell that contains data and select a cell in the next column. Press Ctrl + Shift + Right arrow key. Right click in the selected area and click delete. From the window opened, select delete entire column.



Most probably you will see no change after this. Not to worry. Press Ctrl+S. Close the sheet. When you open the sheet next time, the scroll bar is adjusted

[jaslake]

Hi Abz_Tahar

What about using VBA? are there any ways

Yes, there are ways, perhaps, maybe, could be.

Is there one Column that's filled with Data all the way down to the Last Row with Data?
Is there one Row (perhaps a Header Row) that's filled with Headers all the way to the Right?
Do you have Formatting beyond the End of Rows, Columns that are are not occupied with Data (such as Borders)?
Do all the Worksheets have the Same Structure?

It would help us to help you if you could create and upload a Sample of what your actual 100's of Worksheets look like. VBA Code could be written to do as Pete suggested.

[VBA FTW]

If you have formulas that you've dragged down several hundred rows, you could programatically add those formulas and drag down on the Workbook_Open event. I've found this to reduce file size by a decent amount. Of course it depends how much you're doing this in your workbook. Also, if you have images in your workbook, remove them. There are other things you can do, but without knowing much about your workbook, I can't really offer much else.