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!