EntireColumn.AutoFit - Slow on Large Files???

[Guest]

I have a macro that just cleans up an export from our CAD system. If I have
a small file dump the macro runs smooth and doens't take to long, but if the
file is large it hangs up during the EnitreColumn.AutoFit. Anyone one have
an idea why or a better way to do this?

Here are some examples:

On a small file maybe 125 rows to do

Dim PauseTime, Start, Finish, TotalTime
Start = Timer ' Set start time.

Columns("A:Y").EntireColumn.AutoFit


Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Paused for " & TotalTime & " seconds"

Takes 0.03125 Seconds and to do

Dim PauseTime, Start, Finish, TotalTime
Start = Timer ' Set start time.

Columns("A:A").ColumnWidth = 10.5
Columns("B:B").ColumnWidth = 6.5
Columns("C:C").ColumnWidth = 5
Columns("D").ColumnWidth = 7
Columns("E:E").ColumnWidth = 9
Columns("F:F").ColumnWidth = 40
Columns("G:G").ColumnWidth = 7
Columns("H:H").ColumnWidth = 26
Columns("I:I").ColumnWidth = 6.5
Columns("J:J").ColumnWidth = 4
Columns("K:K").ColumnWidth = 34.5
Columns("L:L").ColumnWidth = 7.5
Columns("M:M").ColumnWidth = 50
Columns("N:N").ColumnWidth = 6.5
Columns("O:O").ColumnWidth = 4.5
Columns("P").ColumnWidth = 36.5
Columns("Q:Q").ColumnWidth = 9
Columns("R:R").ColumnWidth = 24
Columns("S:S").ColumnWidth = 7
Columns("T:T").ColumnWidth = 4
Columns("U:U").ColumnWidth = 11
Columns("V:V").ColumnWidth = 12

Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
MsgBox "Paused for " & TotalTime & " seconds"

Takes 0.0625 Seconds and to do

But if the file is large say 12,500 rows the autofit take 413.7578 seconds
and the manual setting of the columns takes .0625 seconds???

Help?

 

[STEVE BELL]

Steve,

In Excel 2000: I filled columns A:Z and rows 1:13000 with =Rand()

Ran your autofit code, and your manual fit code. Both timed out at under 1
second.

Don't know what else to say...

 

[Guest]

Steve,
I have just run your code (unchanged) on 65500+ rows - cols A:Y
filled with a random number - and got time of approximately 3.0 secs and 0.1
secs! (using Excel 2003). I reversed the order i.e autofit after manual
setting, and got the same times.

I can't offer an explanation!

 

[prepotency]

It's not a linear function with respect to time: in other words, the
more columns you autofit doesn't necessarily increase the time that the
function takes linearly. The autofit exibits behavior more like a log
function and plateaus with respect to the time it takes.
I often use autofit in my data comparison programs with sheets having

10,000 rows and 20+ columns and it doesn't take any time at all.

garrett (MIS)