{"id":56013,"date":"2008-03-13T00:14:00","date_gmt":"2008-03-13T00:14:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/03\/13\/hey-scripting-guy-how-can-i-sort-a-spreadsheet-by-cell-color\/"},"modified":"2008-03-13T00:14:00","modified_gmt":"2008-03-13T00:14:00","slug":"hey-scripting-guy-how-can-i-sort-a-spreadsheet-by-cell-color","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-sort-a-spreadsheet-by-cell-color\/","title":{"rendered":"Hey, Scripting Guy! How Can I Sort a Spreadsheet By Cell Color?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! We have an Excel spreadsheet in which we use different colors as a way to track status information. That works out really good for us, except for one thing: sometimes we\u2019d like to sort the spreadsheet by color. As near as I can tell, however, Excel doesn\u2019t let<\/i> you sort a worksheet by color. Could a script help me out here?<\/p>\n

— GD<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, GD. Could a script help you out here? You bet it can. (Ah, if only Adam Sandler would ask that same question before he makes his next movie.) The fact of the matter is that scripts are always<\/i> useful. Remember the story of Ernest Shackleton<\/b><\/a>? Well, back in 1915, when his ship (the Endurance) became trapped in the ice, Shackleton and his men were marooned in the frozen wasteland of Antarctica. How did they manage to escape certain doom? That\u2019s right: Shackleton wrote a script that notified rescuers of their location, and saved them all.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. OK, if you want to get picky, that\u2019s not entirely<\/i> true; instead, Shackleton and a handful of men hopped in a lifeboat and crossed 800 miles of treacherous ocean in order to get help. It just seemed a little more dramatic to suggest that he wrote a script. After all, where\u2019s the drama and excitement in crossing 800 miles of treacherous ocean in a lifeboat?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Of course, there are two things that separate Ernest Shackleton from the rest of us. For one, we rarely have to cross 800 miles of treacherous ocean in a lifeboat. (Although many, many<\/i> people at Microsoft have suggested that the Scripting Guys give that a try.) For another, any time we get into a bind (yes, any<\/i> time) we can solve all our problems (yes, all<\/i> our problems) simply by writing a script:<\/p>\n

Const xlAscending = 1\nConst xlYes = 1\n\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(\"Sheet1\")\n\ni = 2\n\nDo Until objExcel.Cells(i, 1) = \"\"\n    intColor = objExcel.Cells(i,2).Interior.ColorIndex \n    Select Case intColor\n        Case 3 intSortOrder = 4\n        Case 4 intSortOrder = 1\n        Case 6 intSortOrder = 2\n        Case 41 intSortOrder = 3\n    End Select\n    objExcel.Cells(i, 3) = intSortOrder\n    i = i + 1\nLoop\n\nSet objRange = objWorksheet.UsedRange\nSet objRange2 = objExcel.Range(\"C2\")\nobjRange.Sort objRange2, xlAscending, , , , , , xlYes\n\nSet objRange = objRange2.EntireColumn\nobjRange.Clear\n<\/pre>\n
Before we launch into the details of the script and how it works, let\u2019s set the stage a little. We\u2019re assuming that GD has a spreadsheet that looks something like this:<\/p>\n
\"Microsoft <\/p>\n
Well, yes, now that you mention it, that is<\/i> kind of pretty, isn\u2019t it? But that\u2019s not really important. What is<\/i> important is that GD wants to be able to sort the worksheet so it looks like this:<\/p>\n
\"Microsoft <\/p>\n
As he noted, however, Excel doesn\u2019t have a built-in method for sorting a worksheet by cell color. That\u2019s true when it comes to scripting as well: there\u2019s no straightforward way to use a script to sort cells by background color, either. So what are we going to do about that? The same thing Ernest Shackleton did: we\u2019re going to cheat.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. How can crossing 800 miles of treacherous ocean in a lifeboat be considered cheating<\/i>? Well \u2026 the thing is \u2026 well, it just is, OK?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
As you can see, our spreadsheet contains a list of names in column A and a bunch of colors in column B. What we\u2019re going to do is determine which color appears in column B, then write an integer value representing that color in column C. Once we\u2019ve done that, we\u2019ll sort the worksheet on those integer values, then delete the contents on column C. The net effect? All the like colors will be grouped together, as if we had sorted the worksheet by color. (Which we did, in a somewhat roundabout way.)<\/p>\n
Don\u2019t worry; this will make more sense once we walk through the code. We start out by defining a pair of constants, xlAscending (which we\u2019ll use to sort the worksheet in ascending order) and xlYes (which we\u2019ll use to tell the script that our worksheet has a header row). After we define the constants we create an instance of the Excel.Application<\/b> object, then set the Visible<\/b> property to True; that gives us a running instance of Excel that we can see on screen. As soon as that\u2019s done we use the following two lines of code to open the spreadsheet C:\\Scripts\\Test.xls, and to bind to the first worksheet in that file:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(\"Sheet1\")\n<\/pre>\n
That brings us to this line of code:<\/p>\n
i = 2\n<\/pre>\n
What we\u2019re going to do next is examine the value of column A for each and every row in the spreadsheet (or, to be more precise, each and every row in the spreadsheet that actually has<\/i> a value in column A). We\u2019ll use the counter variable i to keep track of our current row in the worksheet. Because our data doesn\u2019t start until row 2 (row 1 is a header row), we set the initial value of our counter variable to 2.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. What\u2019s that? Would the Endurance still have gotten stuck in the ice if Ernest Shackleton had set the initial value of his<\/i> counter variable to 2? Historians remain divided on that point.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
So how do<\/i> we look at the value of column A for each row in the spreadsheet (or, to be more precise, each and every row in the spreadsheet that actually has a value in column A)? That\u2019s easy; we simply set up a Do loop like this one:<\/p>\n
Do Until objExcel.Cells(i, 1) = \"\"\n<\/pre>\n
As you can see, we\u2019re going to start out by looking at the value of cell A2 (row 2, column 1). If cell A2 is blank we\u2019ll exit the loop. If cell A2 isn\u2019t<\/i> blank then we\u2019ll execute this block of code:<\/p>\n
intColor = objExcel.Cells(i,2).Interior.ColorIndex \nSelect Case intColor\n    Case 3 intSortOrder = 4\n    Case 4 intSortOrder = 1\n    Case 6 intSortOrder = 2\n    Case 41 intSortOrder = 3\nEnd Select\nobjExcel.Cells(i, 3) = intSortOrder\ni = i + 1\n<\/pre>\n
In the first line of this code block we\u2019re simply assigning the value of cell B2 (row 2, column 2) to a variable named intColor; we do that by grabbing the value of the cell\u2019s Interior.ColorIndex<\/b> property. Background colors are stored as integer values; in this case, we have the following values:<\/p>\n\n<\/thead>\n\n\n\n\n\n\n
\n
Color<\/b><\/p>\n<\/td>\n
\n
Yellow<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Red<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Green<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n<\/tr>\n
\n
Yellow<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n<\/tr>\n
\n
Blue<\/p>\n<\/td>\n
\n
41<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n\n<\/thead>\n\n\n
\n
Note<\/b>. How did we know<\/i> these were the corresponding color values? To tell you the truth, we didn\u2019t. Instead, we let a script taken from this article<\/b><\/a> figure that out for us.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Our next step is to set up a Select Case block to take action based on the value of intColor. What kind<\/i> of action are we going to take? We\u2019re going to assign a sort order to each row based on the background color of the cell in column B. We want to sort our colors in the following order:<\/p>\n\n\n\n\n\n\n
\u2022<\/td>\n\n
Green<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Yellow<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Blue<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
Red<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
What does that mean? Well, yellow is the second color in the sort order. Suppose we look at cell B2 and it turns out to be yellow. (Which it is.) Yellow is color No. 2 in the sort order, so we\u2019re going to set the value of cell C2 (row 2, column 3) to 2:<\/p>\n
objExcel.Cells(i, 3) = intSortOrder\n<\/pre>\n
But how did intSortOrder get set to 2? That\u2019s what we did in our Select Case block:<\/p>\n
Case 6 intSortOrder = 2\n<\/pre>\n
As we noted earlier, yellow has an integer value of 6. In the poreceding line of code, we\u2019re checking to see if the background color of cell B2 is equal to 6. If it is, then we assign the value 2 (sort order No. 2) to the variable intSortOrder.<\/p>\n
After incrementing the value of the counter variable i by 1, we go back to the top of the loop and repeat the process with the next row in the spreadsheet. When all is said and done, our worksheet should look like this:<\/p>\n
\"Microsoft <\/p>\n
Now it\u2019s time to sort the worksheet by \u201ccolor\u201d (that is, by the value in column C). That\u2019s what these three lines of code are for:<\/p>\n
Set objRange = objWorksheet.UsedRange\nSet objRange2 = objExcel.Range(\"C2\")\nobjRange.Sort objRange2, xlAscending, , , , , , xlYes\n<\/pre>\n
In line 1 we create an instance of Excel\u2019s Range<\/b> object, an instance that encompasses the range of cells in the worksheet that contain data. (That\u2019s what the UsedRange<\/b> property is for.) In line 2, we create a second range object, this one consisting of the single cell C2. <\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. As it turns out, we could have picked any<\/i> cell in column C; because we want to sort by column C we simply need to create a range consisting of a cell (any cell) from column C. Cell C2 seemed as good as any.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Finally, in line 3 we sort the worksheet, using the object reference to column C (objRange2) as the sort column, and telling Excel that we: 1) want to sort in ascending order (xlAscending); and, 2) have a header row (xlYes).<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. What are all those extra commas for? Well, we don\u2019t have time to go through all the mechanics of sorting data in Excel, at least not today. But this article<\/b><\/a> should tell you everything you need to know.<\/p>\n
At least when it comes to sorting data in Excel.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
All we have to do now is to delete the values we added to column C. So how do we do that? Well, the easiest way is to create a range object that encompasses all the cells in column C. Because we already have a range object that points to cell C2 we can use the EntireColumn<\/b> property to grab the remaining cells in that column<\/b>:<\/p>\n
Set objRange = objRange2.EntireColumn\n<\/pre>\n
After that we simply call the Clear<\/b> method and delete all the values in column C:<\/p>\n
objRange.Clear\n<\/pre>\n
The net result? Well, give it a try and you\u2019ll see the net result.<\/p>\n
And there you have it, GD. Is that more impressive than crossing 800 miles of treacherous ocean in a lifeboat? Probably not. But it\u2019s awfully close.<\/p>\n
P.S. We should probably note that the Scripting Guys are actually somewhat in awe of what Ernest Shackleton accomplished; pretty darn impressive, to say the least. In addition, we also feel a sort of kinship with Shackleton, or at least with the men who joined him on the expedition. According to an apocryphal story, those men joined the crew after responding to the following ad:<\/p>\n\n<\/thead>\n\n\n
\n
Men Wanted<\/b>: For hazardous journey. Small wages, bitter cold, long months of complete darkness, constant danger, safe return doubtful. Honour and recognition in case of success.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
Amazingly enough, that\u2019s the exact same ad the Scripting Guys responded to when they were hired by Microsoft!<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. So did we at least get the honor and recognition that was promised in case of success? Tell you what: as soon as we have<\/i> a success we\u2019ll let you know.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! We have an Excel spreadsheet in which we use different colors as a way to track status information. That works out really good for us, except for one thing: sometimes we\u2019d like to sort the spreadsheet by color. As near as I can tell, however, Excel doesn\u2019t let you sort a worksheet […]<\/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-56013","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! We have an Excel spreadsheet in which we use different colors as a way to track status information. That works out really good for us, except for one thing: sometimes we\u2019d like to sort the spreadsheet by color. As near as I can tell, however, Excel doesn\u2019t let you sort a worksheet […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56013","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=56013"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56013\/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=56013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}