| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with Very slow code Please
Hi all,
Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Try turning off calculation and screen updating...
With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Hi Jim, i do that in a previous module but it is still slow, or should one do
it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
You need to do that for each tread of execution. By that I mean that if you
have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Thanks a million Jim, your help is as always much appreciated.
18:15 here, Great evening/Day -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Hi Jim, sorry i timed the code with the below and it took 65 seconds ??
-- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Hi Jim, sorry finger trouble... I used this timer of Thom Ogilvy, giving me a
result of 65 seconds ?? Sub TimeMyMacro() Dim sngStart As Single, sngEnd As Single sngStart = Timer Call InsertColumns ' call your macro sngEnd = Timer MsgBox "It took " & Format(sngEnd - sngStart, "0") & " seconds" End Sub Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
I tried it on a blank sheet and I got 0.020 seconds. Do you have any events
code in your project??? if so then potentially you will need to disable the events... Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual .enableevents = false End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .enableevents = true .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, sorry finger trouble... I used this timer of Thom Ogilvy, giving me a result of 65 seconds ?? Sub TimeMyMacro() Dim sngStart As Single, sngEnd As Single sngStart = Timer Call InsertColumns ' call your macro sngEnd = Timer MsgBox "It took " & Format(sngEnd - sngStart, "0") & " seconds" End Sub Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Hi Jim, no event codes and i get the same on a blank document ?
May i send the doc to you ? -- Les "Jim Thomlinson" wrote: I tried it on a blank sheet and I got 0.020 seconds. Do you have any events code in your project??? if so then potentially you will need to disable the events... Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual .enableevents = false End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .enableevents = true .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, sorry finger trouble... I used this timer of Thom Ogilvy, giving me a result of 65 seconds ?? Sub TimeMyMacro() Dim sngStart As Single, sngEnd As Single sngStart = Timer Call InsertColumns ' call your macro sngEnd = Timer MsgBox "It took " & Format(sngEnd - sngStart, "0") & " seconds" End Sub Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Jim, the document was downloaded as an excel html file but then opened and
saved as excel. Could this be the problem ?? Even deleting the column takes long ?? It has 6290 rows and is up to column AD -- Les "Jim Thomlinson" wrote: I tried it on a blank sheet and I got 0.020 seconds. Do you have any events code in your project??? if so then potentially you will need to disable the events... Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual .enableevents = false End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .enableevents = true .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, sorry finger trouble... I used this timer of Thom Ogilvy, giving me a result of 65 seconds ?? Sub TimeMyMacro() Dim sngStart As Single, sngEnd As Single sngStart = Timer Call InsertColumns ' call your macro sngEnd = Timer MsgBox "It took " & Format(sngEnd - sngStart, "0") & " seconds" End Sub Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
Send it to me... I'll take a look... If it is big Zip the file and change the
extension name as our e-mail filter strips out zip files. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, no event codes and i get the same on a blank document ? May i send the doc to you ? -- Les "Jim Thomlinson" wrote: I tried it on a blank sheet and I got 0.020 seconds. Do you have any events code in your project??? if so then potentially you will need to disable the events... Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual .enableevents = false End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .enableevents = true .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, sorry finger trouble... I used this timer of Thom Ogilvy, giving me a result of 65 seconds ?? Sub TimeMyMacro() Dim sngStart As Single, sngEnd As Single sngStart = Timer Call InsertColumns ' call your macro sngEnd = Timer MsgBox "It took " & Format(sngEnd - sngStart, "0") & " seconds" End Sub Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help needed with Very slow code Please
I took a look at your file and it appears as if there is some kind of an
issue based on the formatting. Since this is downloaded from HTML your guess is as good as mine. Here is a fix for you. Copy the contents of the sheet and paste special values and number formats into a new sheet. Do not copy the formats as that seems to be what is causing the issue. Now when you run the code it should be extremely quick. Since there are no formulas or events code you can remove that from my suggested solution. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Send it to me... I'll take a look... If it is big Zip the file and change the extension name as our e-mail filter strips out zip files. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, no event codes and i get the same on a blank document ? May i send the doc to you ? -- Les "Jim Thomlinson" wrote: I tried it on a blank sheet and I got 0.020 seconds. Do you have any events code in your project??? if so then potentially you will need to disable the events... Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual .enableevents = false End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .enableevents = true .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, sorry finger trouble... I used this timer of Thom Ogilvy, giving me a result of 65 seconds ?? Sub TimeMyMacro() Dim sngStart As Single, sngEnd As Single sngStart = Timer Call InsertColumns ' call your macro sngEnd = Timer MsgBox "It took " & Format(sngEnd - sngStart, "0") & " seconds" End Sub Sub InsertColumns() ' With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With 'PctDone = Counter + 0.33 'Call UpdateProgress(PctDone) 'InsertCarAge End Sub -- Les "Jim Thomlinson" wrote: You need to do that for each tread of execution. By that I mean that if you have procedures calling other procedures that is a thread of execution. So in your example you call UpdateProgress. You do not need to disable screen updating or calculation in that procedure but you do want to do it in the procedure that you posted. -- HTH... Jim Thomlinson "Les" wrote: Hi Jim, i do that in a previous module but it is still slow, or should one do it for each module ?? -- Les "Jim Thomlinson" wrote: Try turning off calculation and screen updating... With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With -- HTH... Jim Thomlinson "Les" wrote: Hi all, Can anybody tell me why this code is so slow ? the rows of the sheet could be from 10 to over 20,000 Sub InsertColumns() ' Columns("L:L").Insert Shift:=xlToRight Range("L11") = "Age of Car" & Chr(10) & "(Days)" Columns("L:L").ColumnWidth = 11 Columns("L:L").NumberFormat = "0.00" Columns("O:O").Insert Shift:=xlToRight Range("O11") = "Responsible QMT" Columns("O:O").ColumnWidth = 25 Columns("Z:Z").Insert Shift:=xlToRight Range("Z11") = "Rand" Columns("Z:Z").NumberFormat = "$ #,##0.00" Columns("AB:AB").Insert Shift:=xlToRight Range("AB11") = "Rand" Columns("AB").NumberFormat = "$ #,##0.00" Columns("AD:AD").Insert Shift:=xlToRight Range("AD11") = "Rand" Columns("AD").NumberFormat = "$ #,##0.00" Columns("AF:AF").Insert Shift:=xlToRight Range("AF11") = "Rand" Columns("AF").NumberFormat = "$ #,##0.00" Columns("AH:AH").Insert Shift:=xlToRight Range("AH11") = "Rand" Columns("AH").NumberFormat = "$ #,##0.00" Columns("AJ:AJ").Insert Shift:=xlToRight Range("AJ11") = "Rand" Columns("AJ").NumberFormat = "$ #,##0.00" Columns("AL:AL").Insert Shift:=xlToRight Range("AL11") = "Rand" Columns("AL").NumberFormat = "$ #,##0.00" Range("A1").Select PctDone = Counter + 0.33 Call UpdateProgress(PctDone) InsertCarAge End Sub Thanks in advance for any help. -- Les |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| expert help needed for better alternative to slow workbook | Excel Worksheet Functions | |||
| Slow code | Excel Programming | |||
| very slow code | Excel Programming | |||
| Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
| Excel Experts Needed: Marcors on Shared Documents are slow.... | Excel Programming | |||
Linear Mode
