Help needed with Very slow code Please

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

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.
 
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
 
Hi Jim, i do that in a previous module but it is still slow, or should one do
it for each module ??
 
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.
 
Thanks a million Jim, your help is as always much appreciated.

18:15 here, Great evening/Day
 
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
 
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
 
Hi Jim, no event codes and i get the same on a blank document ?

May i send the doc to you ?
 
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
 
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.
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with VBA code for an Excel Model Macro 5
Repost! Excel multiple worksheet populate from recordset 1
Surely there's a better way.... 2
Macro works on one computer but not another 3
Help for final touch up to my code 3
How do I hide certain columns in a sheet with merged cells in a row using a macro? 5
Code not woring - please help!!! 4
need help with macro for 400 worksheets! 2

Back
Top