Which part of my autofilter code doesn't work in 2003?

**jamesryan** · 03-19-2010, 04:50 AM

Hi,

I have a large amount of code which error's when running in Excel 2003. (it does not sort, whereas in 2007 it does).

As i don't have Excel 2003 can someone tell me which part of my code does not run in Exccel 2003 please:

Range("E5").Activate
    ActiveWorkbook.Worksheets("BranchList").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("BranchList").Sort.SortFields.Add Key:=Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("BranchList").Sort
        .SetRange Range("B6:AE65513")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thanks

**romperstomper** · 03-19-2010, 05:13 AM

Everything after the first line - there is no Sort object in 2003. You need to use the range's Sort method - seomthing like:

With ActiveWorkbook.Worksheets("BranchList")
.Range("B6:AE65513").Sort Key1:=.Range("E6"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With

**jamesryan** · 03-19-2010, 05:30 AM

cheers Romper, you're a star.

Got it sorted now, although very annoying to find more things that don't work in 2003!!

**romperstomper** · 03-19-2010, 08:05 AM

That's why it's generally best to develop in the earliest version of Excel that you have to support...

**Marcol** · 03-19-2010, 05:23 AM

To add to romperstomper might be worth adding an If statement using version as a criteria

    Range("E5").Activate
    If Application.Version >= 12 Then
        ActiveWorkbook.Worksheets("BranchList").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("BranchList").Sort.SortFields.Add Key:=Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("BranchList").Sort
            .SetRange Range("B6:AE65513")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Else
        With ActiveWorkbook.Worksheets("BranchList")
            .Range("B6:AE65513").Sort Key1:=.Range("E5"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        End With
    End If

**Marcol** · 03-19-2010, 08:13 AM

romperstomper

Agreed, I only suggested the If statement because I like the "new" 2007 sort functions!

**romperstomper** · 03-19-2010, 08:17 AM

If you are using Option Explicit, the version check still won't work due to the fact that xlSortOnValues does not exist in 2003, so you would have to declare that constant or use the literal value (0) instead.

**Marcol** · 03-19-2010, 08:36 AM

Didn't think of that.

I didn't check using Option Explicit, I'll remember that!

Thanks for the tip.

Which part of my autofilter code doesn't work in 2003?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Re: Which part of my autofilter code doesn't work in 2003?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions