|
Subject:
|
Failure to speed up macro
|
|
Posted By:
|
jeroen999
|
Post Date:
|
5/8/2006 3:09:11 PM
|
Hello everyone,
I am having difficulties in speeding up a macro. I read in Excel 2003 VBA Programmers reference that in order to make your macro as fast as possible you should avoid selecting ranges in your code. So I rewrote my macro and succeeded in removing all the select commands. The resulting gain in speed is however a bit disappointing and it is hardly any faster than my first macro, using the select commands. The macro I use performs on average about 3000 calculations and normally runs about 3 hours. Since I am doing research and have to run these calculations approximately hundreds/thousand times to come, any gain in speed would be very welcome. Is it normal that the gain in speed resulting from removing select commands is hardly noticeable?
I would really appreciate it if anyone could give me some comment or tips. Below I have put the 'old' and 'new' code. Both run good, but as I said there is hardly any difference in speed.
Thank you all!
Jeroen
OLD CODE (WITH SELECT):
Application.ScreenUpdating = False
Dim nroftests As Integer
Dim x As Integer
Dim myrange
nroftests = Range("da2")
Range("db9").Select
For x = 1 To nroftests
Range("n3") = ActiveCell
ActiveCell.Offset(0, 1).Select
Range("s3") = ActiveCell
ActiveCell.Offset(1, -1).Select
Calculate
Set myrange = ActiveCell
Columns("AU:Bk").Select
Selection.Copy
Range("Df1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("Dq1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.LargeScroll Down:=1
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.Copy
Range("Dx1").Select
Selection.CurrentRegion.Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
myrange.Select
Next x
End Sub
NEW CODE (WITHOUT SELECT)
Application.ScreenUpdating = False
Dim nroftests As Integer
Dim x As Integer
Dim topdata1 As Range
Dim topdata2 As Range
Dim datarange As Range
Dim sortrange As Range
Dim goal As Range
Dim goalrange As Range
Set topdata1 = Range("db9:db50000")
Set topdata2 = Range("dc9:dc50000")
Set datarange = Range("au1:bk60000")
Set goal = Range("df1")
Set goalrange = Range("df1:dv60000")
nroftests = Range("da2")
For x = 1 To nroftests
Range("n3") = topdata1.Cells(x).Value
Range("s3") = topdata2.Cells(x).Value
Calculate
datarange.Copy
goal.PasteSpecial xlPasteValues
goal.PasteSpecial xlPasteFormats
goalrange.Sort key1:=Range("dq1:dq60000"), order1:=xlAscending
goalrange.SpecialCells(xlCellTypeConstants, 1).Copy
Range("dx1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
ActiveCell.PasteSpecial xlPasteFormats
Next x
End Sub
|
|
Reply By:
|
interrupt
|
Reply Date:
|
5/16/2006 5:39:37 AM
|
Copying and Pasting huge data ranges like this is bound to give excel a headache - Even transferring data cell by cell using .value and a few clever For loops would probably be faster! (a headache to code though)!
|
|
