VBA ClearContents

**VAer** · 05-15-2017, 09:09 AM

I was doing massive of copying/pasting just now(below code is example) on multiple sheets, before doing that, the file size is less than 200kb, but the file size is almost 100MB now. It appears that ClearContents does not reduce the file size.

The purpose of doing that: copying format (vertical alignment, horizontal alignment, text wrap, font size, font color, etc) to many rows, each column has different format.

Why, how to reduce the file size? I saved the file and reopened it, now the file size is almost 100MB.

Is it because of format in so many cells? If so, I will delete those rows, instead of clearcontents.

Thanks.

Please Login or Register  to view this content.

**sintek** · 05-15-2017, 09:15 AM

Yep I think delete rows would be ideal.... Even though you are clearing, sure the formats etc remain..

**TMS** · 05-15-2017, 09:18 AM

You're going through a loop nearly 100 times and copying 12 columns by nearly 20,000 rows and you're multiplying the target row by 10,000 so I'm guessing you've created around a million rows of data.

Even if you clear the contents, Excel will still consider those rows to have data.