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.
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.
No comments:
Post a Comment