How Can I Sort a Row in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I sort a row in an Excel spreadsheet?

— RF

Hey, RF. You know, this is a tough question, but as a resident of the state of Washington the Scripting Guy who writes this column doesn’t shy away from a challenge. Therefore, he fully intends to address this matter … in the year 2013.

In case you’re wondering about that, a few years ago the Washington state legislature – concerned that our public schools were graduating students who had yet to master the fundamentals of reading, writing, and arithmetic – passed a law stating that no one could graduate from a Washington state high school without passing the WASL (a standardized achievement test). The class of 2008 was targeted as the first group that had to pass the WASL in order to graduate.

As it turned out, a large percentage of students failed the math portion of the test. (Some students failed the reading and/or the writing portions, but math was by far the major bugaboo.) That’s a shame, but, then again, that is what the test was designed to do: identify students who don’t meet a minimum level of competency. Granted, that’s a tough pill to swallow, but, in turn, it should help encourage the schools to do a better job. Besides, it’s not as hopeless as it might sound; after all, students can retake the test as many times as they need to until they finally pass.

So how did the state of Washington respond to this situation? The only way it could: by declaring that students didn’t have to pass the math portion of the WASL after all; instead, all they had to do was pass the reading and writing portions. One day a huge numbers of students had flunked the WASL; the next day, like magic, most of those found out they’d passed the test after all. The system works!

Of course, in theory, the class of 2013 will have to pass the math portion of the test. If they don’t, then they will not graduate from high school. And this time we mean it.

Unless, of course, we don’t.

Fortunately, the Scripting Guys don’t have the patience to wait until the year 2013:

Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1, 1) = “C”
objWorksheet.Cells(1, 2) = “D”
objWorksheet.Cells(1, 3) = “B”
objWorksheet.Cells(1, 4) = “A”
Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows

So how does this script work? Well, we don’t mean to alarm residents of the state of Washington, but we start off with something that looks a lot like math. But don’t worry, this code doesn’t involve actual arithmetic; instead, we’re simply assigning values to three different constants:

After defining our constants we create an instance of the Excel.Application object and set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We then use these two lines of code to create a new workbook and to bind to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

Now, as far as the Scripting Guys are concerned, sorting data isn’t much fun unless you actually have some data to sort. Therefore, our next block of code simply inserts the values C, D, B, and A in cells A1 through D1 (that is, in the first four cells in the first row of our spreadsheet):

objWorksheet.Cells(1, 1) = “C”
objWorksheet.Cells(1, 2) = “D”
objWorksheet.Cells(1, 3) = “B”
objWorksheet.Cells(1, 4) = “A”

Now comes the good part. Before you can sort data you must create a range that encompasses all the data to be sorted. We want to sort the data in row 1, so we use this line of code to turn row 1 into a range:

Set objRange = objExcel.ActiveCell.EntireRow

As you can see, there’s not much to this. By default, the ActiveCell happens to be the first cell in the spreadsheet (unless we specifically make a different cell the ActiveCell). By referencing both the ActiveCell and the EntireRow method we can easily select all the cells in row 1.

Good question: what if we have multiple rows that need to be sorted? In that case, you’ll need to sort each row individually, which also means that you have to create a different range for each row of data. How do you do that? Well, one way is to change the ActiveCell any time you start a new row. Here’s a simple way to do that:

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = “A” & intNewRow
objExcel.Range(strNewCell).Activate

What we’re doing here is adding 1 to the row for the current ActiveCell; if we’re currently in row 1, that makes intRow equal to 2. We then tack the letter A on to the front of the row number; that makes strNewCell equal to A2. Why A2? Well, A2 just happens to be a cell in row 2. By identifying a cell in row 2 we can then use the Activate method to move the active cell to the next row.

And, sure, there are other ways of doing this. If you have a better approach then by all means use that approach. Otherwise, this tactic will do the trick.

Now, where were we? Oh, right: sorting a row of data. After we’ve created a range encompassing the row in question we can sort that data using this command:

objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows

Admittedly, this is a strange-looking line of code, what with the commas and empty parameters and all. That’s because Excel offers a number of sort options (including the ability to sort first by one column of data and then by another) that we aren’t interested in. However, because the parameters we provide to the Sort method must appear in a specific order we can’t just leave out these items; if we did, that would change the order and lead to all sorts of problems. To make sure that everything ends up exactly where it needs to we have to insert blank placeholders for each option we want to ignore. That’s exactly what we’ve done here.

For now, the important thing is to note that we specified the range of data to be sorted (objRange), and used our three constants to indicate that we: 1) want to sort the data in ascending order, 2) don’t want to use a header row, and 3) want to sort the data by rows rather than by columns. Will our data actually get sorted when we run this script? Give it a try and see for yourself.

We hope that answers your question, RF. As for the state of Washington, well, Homer Simpson probably said it best: if something is hard to do, then it’s not worth doing. An important lesson for all of us, not just high school students.

ScriptingGuy1

Follow