Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EntireColumn.AutoFit - Slow on Large Files???
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: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: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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EntireColumn.AutoFit - Slow on Large Files???
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... -- steveB Remove "AYN" from email to respond "Steven M. Britton" wrote in message ... 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: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: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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EntireColumn.AutoFit - Slow on Large Files???
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! "Steven M. Britton" wrote: 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: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: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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EntireColumn.AutoFit - Slow on Large Files???
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) -- prepotency ------------------------------------------------------------------------ prepotency's Profile: http://www.excelforum.com/member.php...o&userid=24155 View this thread: http://www.excelforum.com/showthread...hreadid=377820 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large Spreadsheet Too Slow 2003 | Excel Discussion (Misc queries) | |||
Autofit (Columns.EntireColumn.AutoFit) does not work | Excel Discussion (Misc queries) | |||
AutoFilter Best Practice when used in large files (slow system dow | Excel Discussion (Misc queries) | |||
Autofit in excel - cell is large than what is needed or wanted & . | Excel Discussion (Misc queries) | |||
EXCEL "EntireColumn.AutoFit" Help! | Excel Programming |