Can anyone tell me why this code fails in Excel 2003 (works fine in Excel 2007)

I get the following error:

object doesn't support this property or method

and the line ActiveSheet.Sort.SortFields.Clear is highlighted


Sub Sort_NoneReturned()
'
'

'
Application.EnableEvents = False
Application.ScreenUpdating = False

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"O3:O5000"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:O5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("B2").Select

Application.EnableEvents = False
Application.ScreenUpdating = True

End Sub

xl2007 has lots more features that xl2003 lacks:

With ActiveSheet.Range("A2:o5000")
.Cells.Sort key1:=.Columns(15), order1:=xlAscending, Header:=xlYes, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

You could add
dataoption1:=xlSortNormal
But that was added in xl2002 (IIRC). Don't include it if you have to support
xl2k, too.

ordnance1 wrote:

Can anyone tell me why this code fails in Excel 2003 (works fine in Excel 2007)

I get the following error:

object doesn't support this property or method

and the line ActiveSheet.Sort.SortFields.Clear is highlighted

Sub Sort_NoneReturned()
'
'

'
Application.EnableEvents = False
Application.ScreenUpdating = False

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"O3:O5000"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A2:O5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("B2").Select

Application.EnableEvents = False
Application.ScreenUpdating = True

End Sub

--

Dave Peterson