# Excel 2007 question **URL:** \ **Category:** Factual Questions\ **Created:** [May 19, 2010, 7:47pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043 "2010-05-19T19:47:35Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Rebo](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rebo/32/13005_2.png) [@Rebo](https://boards.straightdope.com/u/Rebo)\ **Post date:** [May 19, 2010, 7:47pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/1 "2010-05-19T19:47:35Z")
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! ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [May 19, 2010, 7:57pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/2 "2010-05-19T19:57:58Z")
sorry, just realized a problem with that answer ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [May 19, 2010, 8:37pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/3 "2010-05-19T20:37:11Z")
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. ---
**Author:** ![Gary\_Robson](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/gary_robson/32/3448_2.png) [@Gary\_Robson](https://boards.straightdope.com/u/Gary_Robson)\ **Post date:** [May 19, 2010, 8:53pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/4 "2010-05-19T20:53:41Z")
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. ---
**Author:** ![Rebo](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rebo/32/13005_2.png) [@Rebo](https://boards.straightdope.com/u/Rebo)\ **Post date:** [May 19, 2010, 8:56pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/5 "2010-05-19T20:56:13Z")
> [@Darth\_Panda](#): > > 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. The range changes. ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [May 19, 2010, 9:03pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/6 "2010-05-19T21:03:31Z")
Yeah, figured as much - recording the macro won’t work for that. The data sort will get recorded as hard code. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [May 19, 2010, 9:17pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/7 "2010-05-19T21:17:34Z")
> [@Darth\_Panda](#): > > Yeah, figured as much - recording the macro won’t work for that. The data sort will get recorded as hard code. 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: ```auto 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 ```auto 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. ---
**Author:** ![Gary\_Robson](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/gary_robson/32/3448_2.png) [@Gary\_Robson](https://boards.straightdope.com/u/Gary_Robson)\ **Post date:** [May 19, 2010, 9:55pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/8 "2010-05-19T21:55:46Z")
> [@Darth\_Panda](#): > > Yeah, figured as much - recording the macro won’t work for that. The data sort will get recorded as hard code. 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. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [May 19, 2010, 11:16pm UTC](https://boards.straightdope.com/t/excel-2007-question/540043/9 "2010-05-19T23:16:03Z")
> [@KneadToKnow](#): > > … > > just edit out the “Range(“a2:j21”).Select” line so it says 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.