How To Sort An Excel List Conditionally Using VBA Code
It’s easy to sort an Excel list using Excel’s standard sorting tools or applying a function directly in VBA code. But it’s a little more challenging to sort a list where you must apply your criteria.
An Example Of Conditional Sorting
A typical scenario might be to sort alphabetically the following list of countries, but always have the big regions like the USA, UK and Japan at the top of the list.
Country New Zealand Australia USA Mexico Belgium UK Japan
We’ll create a new list using some simple VBA code which you’ll be able to adapt to meet your own needs.
Organizing The Code
One solution to this problem is to reorganize the list so the top countries are at the top and then sort the two areas of the list separately.
First, we’ll define the names and number of countries we want to appear at the top of the list.
topItems = ",USA,UK,Japan," ctItems = UBound(Split(topItems, ",")) - 1 Next, we can select the list and set a counter for the number of "top" countries and "others".
Set rng = ActiveCell.CurrentRegion top = 1 others = 1
Now we’re ready to separate out the list into the top countries and others which we’ll do by moving each country into a new list alongside the old one. Don’t forget we need to ignore the header row.
For x = 2 To rng.Rows.Count
If the current cell value is one of the top countries then we’ll move the value to the top of a new list, and if not we’ll move it to the bottom of the new list.
If InStr(topItems, "," & rng.Rows(x) & ",") Then top = top + 1 Cells(top, 2) = rng.Rows(x) Else others = others + 1 Cells(others + ctItems, 2) = rng.Rows(x) End If Next
Our list is now reorganized in the following way, and we just need to sort the bottom part of the list in column 2.
USA UK Japan New Zealand Australia Mexico Belgium
The following code sorts the list below the top countries in column 2. Because we know how many top countries there are, the range begins two rows below that value – to take into account the header row.
Set rng = Range("b" & ctItems + 2 & ":" & ActiveCell.End(xlDown).Address)
rng.Sort Key1:=Range("b1"), order1:=xlAscending
The code produces a final result looking like this:
USA UK Japan Australia Belgium Mexico New Zealand
One area for development might be to arrange the top countries in a certain order. It would be easy enough to hard code a solution, but it is good practice to have a scalable solution; for example it might be a list of customers and you need to highlight your top 100 purchasers.
Summary
This short VBA code provides a solution to a problem not readily solvable by using the standard Excel tools. It’s the type of scenario VBA developers often face and a good candidate for saving in a handy location for future reference.
