Clear Excel 2007 cache

I have an Excel 2007 spreadsheet which extracts large quantities of data from a spreadsheet. Even after I delete the data, the spreadsheet is still very large. I think it's because copies of the data are still stored in the cache.

Is there a way for me to clear the cache (either in Excel itself or by using a VBA macro) without actually deleting the destination cells? I wouldn't want to delete them since doing so would destroy the formatting I need.
RothbardAsked:
Who is Participating?
 
Rory ArchibaldCommented:
What kind of extract? If it's not a pivot table, then there is no data cache. Any formatted cells will increase the size of the used range of a sheet and hence the size of the workbook.
0
 
RothbardAuthor Commented:
I see, so it's probably the formatting of large ranges which is causing the file size to be so large. Alternatively - how can I arrange in VBA for a given cell range to have a particular format?
0
 
Rory ArchibaldCommented:
If you use styles it's simple:
Range("A1:A1000").Style = "Normal"

Open in new window


for example. Otherwise you need to specify each property you want applied.
0
 
RothbardAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.