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

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)!