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.