{"id":65003,"date":"2007-04-25T21:28:00","date_gmt":"2007-04-25T21:28:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/04\/25\/how-can-i-sort-a-row-in-an-excel-spreadsheet\/"},"modified":"2007-04-25T21:28:00","modified_gmt":"2007-04-25T21:28:00","slug":"how-can-i-sort-a-row-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-sort-a-row-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Sort a Row in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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

— RF<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019t shy away from a challenge. Therefore, he fully intends to address this matter \u2026 in the year 2013.<\/P>\n

In case you\u2019re wondering about that, a few years ago the Washington state legislature \u2013 concerned that our public schools were graduating students who had yet to master the fundamentals of reading, writing, and arithmetic \u2013 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.<\/P>\n

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\u2019s a shame, but, then again, that is<\/I> what the test was designed to do: identify students who don\u2019t meet a minimum level of competency. Granted, that\u2019s a tough pill to swallow, but, in turn, it should help encourage the schools to do a better job. Besides, it\u2019s 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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. To tell you the truth, the Scripting Guy who writes this column isn\u2019t a particularly big fan of standardized testing, but rules are rules. And, in the interests of fair disclosure, we should note that the Scripting Son took the WASL and passed all three sections.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

So how did the state of Washington respond to this situation? The only way it could: by declaring that students didn\u2019t 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\u2019d passed the test after all. The system works!<\/P>\n

Of course, in theory, the class of 2013 will<\/I> have to pass the math portion of the test. If they don\u2019t, then they will not<\/I> graduate from high school. And this time we mean it.<\/P>\n

Unless, of course, we don\u2019t.<\/P>\n

Fortunately, the Scripting Guys don\u2019t have the patience to wait until the year 2013:<\/P>

Const xlAscending = 1\nConst xlNo = 2\nConst xlSortRows = 2<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1, 1) = “C”\nobjWorksheet.Cells(1, 2) = “D”\nobjWorksheet.Cells(1, 3) = “B”\nobjWorksheet.Cells(1, 4) = “A”<\/p>\n
Set objRange = objExcel.ActiveCell.EntireRow\nobjRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows\n<\/PRE>\n
So how does<\/I> this script work? Well, we don\u2019t mean to alarm residents of the state of Washington, but we start off with something that looks a lot like math. But don\u2019t worry, this code doesn\u2019t involve actual arithmetic; instead, we\u2019re simply assigning values to three different constants:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
xlAscending<\/B>, which we\u2019ll use to sort the data in ascending (A to Z) order. Can we sort the data in descending (Z to A) order if we want to? Of course we can; in that case we just use the constant xlDescending and assign it the value 2.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
xlNo<\/B>, which we\u2019ll use to indicate that our data does not include a header row. (Which should be obvious, seeing as how we\u2019re sorting a row rather than a column.)<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
xlSortRows<\/B>, which tells the script to sort the selected cells by row rather than by column.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
After defining our constants we create an instance of the Excel.Application<\/B> object and set the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
Now, as far as the Scripting Guys are concerned, sorting data isn\u2019t much fun unless you actually have some data to sort. Therefore, our next block of code simply inserts the values C<\/I>, D<\/I>, B<\/I>, and A<\/I> in cells A1 through D1 (that is, in the first four cells in the first row of our spreadsheet):<\/P>
objWorksheet.Cells(1, 1) = “C”\nobjWorksheet.Cells(1, 2) = “D”\nobjWorksheet.Cells(1, 3) = “B”\nobjWorksheet.Cells(1, 4) = “A”\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Despite the fact that the Scripting Guy who writes this column went to high school in the state of Washington he was still able to determine that this code did, indeed, put data in the first four<\/I> cells in the spreadsheet. And he managed to do that largely on his own, with only a little bit of help.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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:<\/P>
Set objRange = objExcel.ActiveCell.EntireRow\n<\/PRE>\n
As you can see, there\u2019s not much to this. By default, the ActiveCell<\/B> 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<\/B> method we can easily select all the cells in row 1.<\/P>\n
Good question: what if we have multiple rows that need to be sorted? In that case, you\u2019ll 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<\/I>? Well, one way is to change the ActiveCell any time you start a new row. Here\u2019s a simple way to do that:<\/P>
intNewRow = objExcel.ActiveCell.Row + 1\nstrNewCell = “A” & intNewRow\nobjExcel.Range(strNewCell).Activate\n<\/PRE>\n
What we\u2019re doing here is adding 1 to the row for the current ActiveCell; if we\u2019re currently in row 1, that makes intRow equal to 2. We then tack the letter A<\/I> on to the front of the row number; that makes strNewCell equal to A2<\/I>. 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<\/B> method to move the active cell to the next row.<\/P>\n
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.<\/P>\n
Now, where were we? Oh, right: sorting a row of data. After we\u2019ve created a range encompassing the row in question we can sort that data using this command:<\/P>
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows\n<\/PRE>\n
Admittedly, this is a strange-looking line of code, what with the commas and empty parameters and all. That\u2019s 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\u2019t interested in. However, because the parameters we provide to the Sort<\/B> method must appear in a specific order we can\u2019t 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\u2019s exactly what we\u2019ve done here.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. For more information about these other options, and to see some additional examples on sorting data in Excel, see our Office Space article<\/B><\/A> on, well, sorting data in Excel.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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\u2019t<\/I> 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.<\/P>\n
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\u2019s not worth doing. An important lesson for all of us, not just high school students.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019t shy away from a challenge. Therefore, he fully intends to address this matter \u2026 in […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-65003","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019t shy away from a challenge. Therefore, he fully intends to address this matter \u2026 in […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=65003"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65003\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=65003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}