Recording a Sort Macro in 2003 vs 2007

Excel 2007 records the sort operation differently than Excel 2003. In 2007, the range is specified in the SetRange method while in 2003 the Selection object is used. Take this simple checkbook workbook.

If you record a macro to sort on check number in 2003, you get something like this

Sub Macro3()

‘ Macro3 Macro
‘ Macro recorded 2/20/2011 by Dick Kusleika

    Selection.Sort Key1:=Range(“B2”), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
End Sub

Cell A1 is selected, then the current region by using the Goto – Special dialog (F5). The sort is done on the selected range. Contrast that with a macro recorded in 2007.

Sub Macro1()

‘ Macro1 Macro

    ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“B2:B12”) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(“Sheet1”).Sort
        .SetRange Range(“A1:E12”)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
End Sub

In this version, the range A1:E12 is hard-coded. When a new row is added, the 2003 code works the same and the 2007 code fails.

Like many readers of DDoE, I don’t accept recorded code. I only use it to discover objects, properties, and methods necessary. This is a special case. This workbook is used to instruct people who are not just new to Excel, but new to computers in general. Having them open the VBE, much less edit code, is out of the question.

I considered using Lists (Tables in 2007), but they act so differently in the two versions that I ruled it out. Ultimately I want to demonstrate recording a macro that sorts on check number and another macro that resorts on date. Any ideas on how I can accomplish this without editing the code?

Posted in Uncategorized

2 thoughts on “Recording a Sort Macro in 2003 vs 2007

  1. Good one, Dick, and thanks.
    Presumably recorded macros in 2007 are going to be a bigger novice-trap than ever they were in earlier versions of Office.
    I am staggered that MS developers have dropped back to 1997 ways of doing business.

  2. Hi Dick,

    Very late answer but I did see your post… very late! ;-)

    I’m certainly not yet an Excel guru but maybe with something like the following:

    Assume the active cell is the upper left cell of the range to be sorted, Q is the most right column to be sorted and vNb_Rows a variable containing the number of rows to be sorted.
    The “vNb_Row – 1” is there to avoid to sort an empty row.
    Of course, the “B” and “Q” columns could be defined by a variable value.
    It works on both systems I use: W7 with French MSO 2010 and also W7 and Dutch MSO 2010.

    Range(ActiveCell.Address & “:Q” & ActiveCell.Row + vNb_Rows – 1).Select
    ActiveWorkbook.Worksheets(“PublPdf”).Sort.SortFields.Add Key:=Range(“B” & ActiveCell.Row & “:B” & ActiveCell.Row + vNb_Rows – 1), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(“PublPdf”).Sort
    .SetRange Range(ActiveCell.Address & “:Q” & ActiveCell.Row + vNb_Rows – 1)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    End With

    Have a nice day!


Leave a Reply

Your email address will not be published. Required fields are marked *