Why autoformat increases file size?

[kdw]

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 :

> 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!

[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

In article <[email protected]>,
[email protected] 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!
>

[kdw]

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!
>