It is simple enough to sort the list using standard Excel sorting tools or use the function directly in VBA code. But a little harder is to sort the list where you need to apply your own criteria.
Conditional sorting example
A typical scenario can be sorted alphabetically by list of countries, but always has such large regions as the United States, the United Kingdom, and Japan at the top of the list.
A country
New Zealand
Australia
USA
Mexico
Belgium
United Kingdom
Japan
We will create a new list using a simple VBA code that you can customize to meet your own needs.
Code organization
One solution to this problem is to reorganize the list, so the best countries are at the top and then sort the two areas of the list separately.
First, we define the names and the number of countries that we want to display at the top of the list.
topItems = "United States, United Kingdom, Japan,"
ctItems = UBound (Split (topItems, ",")) - 1
Then we can select a list and set a counter for the number of "best" countries and "others."
Set rng = ActiveCell.CurrentRegion
top = 1
others = 1
Now we are ready to divide the list into the best countries and others, which we will do by moving each country to a new list according to the old one. Do not forget that we must ignore the header line.
For x = 2 K rng.Rows.Count
If the current cell value is one of the best countries, then we will move the value to the beginning of the new list, and if we do not 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)
yet
others = others + 1
Cells (other + ctItems, 2) = rng.Rows (x)
End If
following
Our list is now reorganized as follows, and we just need to sort the bottom of the list in column 2.
USA
United Kingdom
Japan
New Zealand
Australia
Mexico
Belgium
The following code sorts the list below the topmost countries in column 2. Because we know how many countries there are, the range starts with two lines below this 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 gives the final result, which looks like this:
USA
United Kingdom
Japan
Australia
Belgium
Mexico
New Zealand
One of the areas of development may be the organization of the best countries in a certain order. It would be easy enough to hard-code the solution, but it would be nice to have a scalable solution; for example, it may be a list of customers, and you need to highlight the top 100 customers.
Summary
This short VBA code provides a solution that cannot be easily resolved with standard Excel tools. This is a type of scenario that VBA developers often encounter, and a good candidate to save in a convenient place for future reference.
