VBA Sort

Learn Excel from MrExcel Podcast, Episode 2093: Sorting With VBA

Hey, welcome back to MrExcel netcast, I'm Bill Jelen.  Today's question from James in Huntsville.  James, I used the macro recorder to record the action of sorting data.  So let's say that James needed to sort this data by Sector and Customers.  So you come out here to the View tab, Macros, Record New Macro, SortMyReports, Shortcut Key Ctrl  – I’ll type Shift+S there, and click OK.  Alright, so then from here we do: Data, Sort, and we want to say that we want to Sort by Sector and then Add a Level and Sort by Customer, and click OK.  Down in the lower left-hand corner we click Stop Recording.  Alright, so there it is.  Seems to have worked, right? 

But here's the problem: tomorrow you're going to have more data or less data or, you know, whatever.  And that Recorded Macro is just horrible.  Let's take a look, I'll do Alt+F8 and take a look at SortMyReport, will Edit that.  Alright, and this is everything that they recorded so the SortFields.Clear, and then they set up a new sort with the SortFields.Add and they’re hard-coded, and there’s 568 rows, and all of the stuff. 

Now sorting back in the day, I was really, really easy.  Alright, and then in Excel 2007 they added Sort by Icon,  Sort by Color, Sort by Font Color, the ability to have 15 Level Sorts and everything just got really, really insane.  So, I don't use the Recorded Macro anymore.  I just go old-school.   

Now, I'm going to switch back to Excel.  Here are the rules for this old-school sorting to work, alright.  Heading above every Column: that heading has to be on one row, not two rows.  If you have titles up there and that's fine to have titles up there.  You need a completely blank row between your titles and the first heading.  If you have notes out on the right-hand side: your wife calls with a grocery list, “Hey, honey, stop on the way home.  Get milk, eggs and vodka.”  There has to be a completely blank column between your data and that.  And if there's boilerplate notes at the bottom, make sure there's a completely blank row between the last bit of data and those notes. 

My whole goal is that we should be able to come to any one cell: the top left-corner cell this data and press Ctrl+* and it will select the data to be sorted.  Now, I'm going to press Ctrl+. that takes us to this corner and then Ctrl+. will take us to the bottom- right corner, Ctrl+. takes us to the bottom-left corner.  Alright, so if Ctrl+* will correctly select your data then everything is great.  If you put your grocery list in Column H and we see we come up here and Ctrl+*, well now, we're sorting grocery list as part of the thing and your grocery list will get screwed up.  Or we'll undo: if this row is not here, now we do Ctrl+* , see we're- now they'll be clueless because they don't have any headings anymore, alright?   

So, if you're going to use my code make sure that all of these rules are true: no sorting my color, no sorting my icon, 3 or less sort levels.  Undo, alright.  So here's what we know: we know that every day our data is going to start in A5.  If we don't know how many rows or how many- well even how many columns we might have.  I can't imagine a situation where the columns are interchanged but certainly the number of rows are going to change.  So Alt+F11, we're simply going to start from that top-left corner cell.  So Range, in my case is “A5”.CurrentRegion.  Current region is this awesome building property that says we're going to press Ctrl+Shift+* and everything that's included there is what's going to be sorted.  And we do .Sort.  .Sort, alright.

Now, here's the thing.  If you want to do a one-level sort is easy: Key1:=.  := and we just say that it's going to be Range – Oh I forget what it is.  It was Sector, where’s Sector?  Sector is in Column C.  So C5 in my case, Range(“C5”) and then ,Order1:=xlAscending.  I pressed the Down Arrow key there, and then Tab.  Alright now, I could keep going out to the right but I'm not going to do that.  I'm going to go to a new line so space, underscore to go to a new line, continues this line of code, alright?  And if I have a second level sort: Key2:= and in this case I want to sort by customer which is in Column D, so D5.  And then, Order2:xlAscending.  Beautiful. 

I don't have a third level sort but if you did, it would be Key3 and then Order3.  And then this next one, the one you have to do is Header, alright?  So, Header:=xlGuess that’s where you’re getting a heck of a lot of trouble.  And so we're going to say xlYes there, definitely as a Header.   Even in the old days, the Macro Recorder would use xlGuess.  I hate for Excel to guess. 

That's it.  One line of code, that's all you have to do and it will work with more rows, less rows.  It's a beautiful, beautiful thing.  Alright, so we're going to come back here to Excel.  Ctrl+Shift+S is still the thing that's assigned.  It now- If you just switched over to VBA and you type that yourself, you can go to Alt+F8, find the name of your Macro, click Options and type Ctrl+Shift+S in there or we can even assign it to a Shortcut key up here on the Quick Access Toolbar.  Right click, Customize Quick Access Toolbar where I choose from our Macros.  I have a Macro called SortMyReport, will click Add - I hate the little flowchart there.  We'll modify that and I would love for there to be some sort of A to Z situation there but of course there's not.  Maybe that arrow who knows, who knows, just choose anything.  The magic 8-ball, I don't know.  I'm going to choose this little guy here, click OK, click OK.  Alright, so now our data is Sorted by Date, I choose - and it doesn't matter what I choose.  It’s always going to go back and sort it from A5, I click the little guy and my data is now Sorted by Sector, within sector, by Customer.  It works great, alright?

So if you're a fan of the Macro Recorder, well my heartfelt best wishes to you.  But the Macro Recorder code these days for sorted- sorting in VBA is just so much simpler; to just go back, simply use essentially this one, one line of code.

Well, this is usually the spot where I try and get you to buy this book, but today I think you should take a look at this book: Excel 2016 VBA and Macros by Tracy and myself.  Wow!  Check this out.  I didn’t realize there is a version in another language.  We’ll get you completely up the Macro Learning Curve from recording your first Macro to the code you need.

Well, simple wrap-up for today: The Excel Macro Recorder does not do a good job with recording, sorting: provided your data can be selected using Ctrl+* that's known as the current region, provided you aren't sorting like color or icon or more than three levels, just use the old-school Range().CurrentRegion.Sort method in VBA to sort.

I want to thank James for sending that question in.  I want to thank you for stopping by.  We’ll see you next time for another netcast from MrExcel.