Solved: Sort Problem 2003 to 2007

[gimli]

Hi All,

Recently converted to 2007 and working thru some issues with some of my spreadsheets that are acting funny now..

This sort listed below used to work fine in 2003 but now when it runs it hangs the workbook up for like 3 or 4 minutes. A little icon flashs in excel at the bottom left say "calculating".

When i run the Sort 1 and 2 seperately they run fine...combined like below doesnt work.

Any suggestions would be great.






Sub Sort()

'Sort 1
Application.ScreenUpdating = False
Dim i As Integer
Columns(11).Insert
For i = 8 To 17
Cells(i, 11) = Abs(Cells(i, 12) - Cells(13, 6))
Next
Range("J8:R17").Sort key1:=Range("K8:K17"), order1:=xlAscending,Header:=xlNo
Columns(11).Delete

'Sort 2
Dim j As Integer
Columns(14).Insert
For j = 8 To 17
Cells(j, 14) = Abs(Cells(j, 15) - Cells(9, 6))
Next
Range("J8:R17").Sort key1:=Range("N8:N17"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
Application.ScreenUpdating = True

End Sub

[Kenneth Hobs]

Turn off autocalc. I posted a kb entry so that SpeedOn, your code, and SpeedOff would do all of that for you.

[gimli]

Turn off autocalc. I posted a kb entry so that SpeedOn, your code, and SpeedOff would do all of that for you.

Ken,

Heres my code now...im calling the subs from within the sort sub..is that the best way?

Option Explicit
Public glb_origCalculationMode As Integer

' BUNCH OF OTHER CODE HERE

Sub Sort1()
Call SpeedOn
Application.ScreenUpdating = False
Dim i As Integer
Columns(11).Insert
For i = 8 To 17
Cells(i, 11) = Abs(Cells(i, 12) - Cells(13, 6))
Next
Range("J8:R17").Sort key1:=Range("K8:K17"), order1:=xlAscending, Header:=xlNo
Columns(11).Delete

' Sort 2
Dim j As Integer
Columns(14).Insert
For j = 8 To 17
Cells(j, 14) = Abs(Cells(j, 15) - Cells(9, 6))
Next
Range("J8:R17").Sort key1:=Range("N8:N17"), order1:=xlAscending, Header:=xlNo
Columns(14).Delete
Application.ScreenUpdating = True
Call SpeedOff
End Sub

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

[Kenneth Hobs]

Yes, that is what we call modular programming.

You can remove the Application.Screenupdating lines in your code since my subs do that.

In the end, let's hope that the calculation mode helps speeds it up for you.

[gimli]

Ken,

It is definately faster but still seem slugish compared to running in Excel 2003...

What is that fill slow fill fast code do?

[Kenneth Hobs]

It just makes it easy to do. You could add the parts that you need as you did. If you don't know what a command does, click the word in the VBE and press F1.

[austenr]

the sluggishness might come from Kens line of code in Speed Off

.CalculateBeforeSave = True
If thats true, then you probably have tons of calcs Excel is doing before it closes. But you probably knew that.

Also to make it easier on your eyes if you have tons of code you could simplify the code above by doing:

Sort1
SpeenOn
SpeedOff

It just calls the subs. I did this recently because I have over 50 subs and couldn't stand all the sub code in the same module. Just a suggestion.

[gimli]

It still runs the code slow..everytime the sort1 sub runs it takes about 5 seconds and the screen does alot of blips...

oh well

[austenr]

Put in this to suppress the screen flickering:

Application.ScreenUpdating = False the first line under Sub1(). Do = TRUE at the end of the major Sub

[gimli]

Hey thanks for the suggestions.

Im marking this solved since the calculation mode does speed up things. 2007 does still seems a tad sluggish..