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!

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 :

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!

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

In article ,
says...
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!

Thank you. I have not worked much with UsedRange before, but will now pay
closed attention.

"keepITcool" wrote:



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 :

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!