Contact Us

Contact SLIM Enterprises today to get the most out of your business.

captcha

How To Sort An Excel List Conditionally Using VBA Code

November 19, 2017

It’s easy enough to sort a 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 need to apply your own 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Archive

Categories

Recent News

Recent Comments