Why autoformat increases file size?

[Guest]

I have a very small range of data (10 columns and about 100 rows) to export
to Excel. If I use AutoFormat like wkbk.range("A:I").AutoFormat, then the
file size is 3MB+, but if I leave out this line, then the size is only about
17k. Anyone knows why? I can get what I need using othe methods like
NumberFormat, but just curious to the why.

Thanks!

 

[keepITcool]

if you use autoformat on the range A:H
then all cells in those columns are formatted..
from row 1 to 65536...

since the used range depends on formatting as well as content...
you now have a large usedrange.. and thus a large file size.

first:
clear the formatting on row 65536
then delete rows 101:65536
then save the file.

it "should" now be ok again.

and next time..
activesheet.usedrange.autoformat






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


kdw wrote :

 

[Tushar Mehta]

Assuming you have a variable named aWS that refers to the *worksheet*
of interest, consider the untested
Application.Intersect( _
aws.Range("A:i"), aws.UsedRange).AutoFormat

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions