ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help needed with Very slow code Please (https://www.excelbanter.com/excel-programming/413140-help-needed-very-slow-code-please.html)

Les
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

Jim Thomlinson
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

Les
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

Jim Thomlinson
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

Les
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

Les
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

Les
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

Jim Thomlinson
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

Les
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

Les
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

Jim Thomlinson
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

Jim Thomlinson
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


All times are GMT +1. The time now is 03:39 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com