Macro Issues Excel 2003 vs 2007

Topic

New Reply

Hello,

I have a macro that was written on a computer with excel 2007 but was saved as an .xls file (2003). When I run the macro on my computer it works fine but if someone that has 2003 on their laptop opens it and attempts to run the macro the receive an error. When they hit debug it appears to be the ” ActiveWorkbook.Worksheets(“Sheet1″).Sort.SortFields.Clear” that is causing the issue for them. Basically, I want to take a few columns and sort them by a specific column. Could you help me write the code so that it won’t bomb out on a user with excel 2003? The full code for the selection and sort is below. Thanks.

Columns(“A:F”).Select
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range( _
“E2:E65536”), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Columns(“A:F”)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Reply | Quote

Replies

Roughly:

Code:

    Range("A:F").Sort Key1:=Range("E2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers

Reply | Quote

MS changed the syntax for sorting in xl2007.
It added no real value. They have no shame.
The following should work in all XL versions from xl97 thru xl2007.
There may be other “improvements” in xl2010 ?
‘–
Sub UniversalSort()
With ActiveSheet
.Columns(“A:F”).Sort key1:=.Range(“E2″), _
order1:=xlDescending, header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
‘–
Jim Cone
Portland, Oregon USA
Review: Special Sort add-in

Reply | Quote

It added no real value. They have no shame.

Depends on your point of view. I’ve seen a fair few posts where people wanted to sort by more than three fields; now they can without doing multiple sorts.

Reply | Quote