Excel 2007 question

Factual Questions
1

Can someone tell me how to write (or write me) a macro for sorting?

In Excel 2003, I can sort two columns by 2nd column, descending, and it remembers it (most of the time.)

In 2007, it never remembers! I have to pick the same picks every time. And what galls me, is the first time I select the two columns, it knows I want to sort by column B, highest to lowest.

I’m really ignorant in the ways of Excel, so talk to me like I’m 6 years old, please.

Thanks for your help!

2

sorry, just realized a problem with that answer

3

Does the data always occupy the same space on the spreadsheet, or does it move around? Writing a macro to sort A8:B20 is really easy; writing a macro to sort a range that changes size (maybe it’s A8:B22 tomorrow) is trickier - and I’m still new to writing macros.

4

The easiest way to do it is to use the macro recording function. Name the range. Start the macro recorder. Use “go to” to select the range. Sort it. Turn off the macro recorder. Done.

5

The range changes.

6

Yeah, figured as much - recording the macro won’t work for that. The data sort will get recorded as hard code.

7

But recording the macro is a great start. You just then have to go in to edit it, removing the hard-coded addresses. Then all you have to do is remember to select what you want to sort before running the macro. There are other ways, but this is about the simplest.

For example, if you record the macro and then have something like this:


    Range("A2:J21").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

just edit out the “Range(“a2:j21”).Select” line so it says


    Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

this will result in a macro that takes whatever cells you select and performs the sort on them. Oh, but you’ll need to make sure that range you’re sorting by (Range(“A2”) in my example) stays the same every time, though.

8

No, you missed the first step in my instructions: name the range.

That way, every time you add lines (assuming you add them within the named range), the range will expand, and since the macro is recorded using a “goto” and the named range, it will always select the correct data.

9

Or… replace that line with:

    Selection.CurrentRegion.Select

Click anyway in the data, then run the macro. The command above will expand the selection to include all rows in the “region”.

This assumes that your data is arranged in a tabular format: no blank rows and the header (if any) is formatted differently than the rows. It is always a good idea to arrange your data this way.