| 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 |
| 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 | |||
Hybrid Mode
