It is simple sufficient to kind a listing utilizing Excel’s normal sorting instruments or making use of a operate instantly in VBA code. However it’s slightly tougher to kind a listing the place you’ll want to apply your personal standards.
An Instance Of Conditional Sorting
A typical state of affairs is likely to be to kind alphabetically the next record of nations, however at all times have the large areas just like the USA, UK and Japan on the prime of the record.
Nation
New Zealand
Australia
USA
Mexico
Belgium
UK
Japan
We’ll create a brand new record utilizing some easy VBA code which you can adapt to fulfill your personal wants.
Organizing The Code
One resolution to this downside is to reorganize the record so the highest international locations are on the prime after which kind the 2 areas of the record individually.
First, we’ll outline the names and variety of international locations we need to seem on the prime of the record.
topItems = ",USA,UK,Japan,"
ctItems = UBound(Cut up(topItems, ",")) - 1
Subsequent, we will choose the record and set a counter for the variety of “prime” international locations and “others”.
Set rng = ActiveCell.CurrentRegion
prime = 1
others = 1
Now we’re able to separate out the record into the highest international locations and others which we’ll do by transferring every nation into a brand new record alongside the outdated one. Remember we have to ignore the header row.
For x = 2 To rng.Rows.Rely
If the present cell worth is among the prime international locations then we’ll transfer the worth to the highest of a brand new record, and if not we’ll transfer it to the underside of the brand new record.
If InStr(topItems, "," & rng.Rows(x) & ",") Then
prime = prime + 1
Cells(prime, 2) = rng.Rows(x)
Else
others = others + 1
Cells(others + ctItems, 2) = rng.Rows(x)
Finish If
Subsequent
Our record is now reorganized within the following manner, and we simply have to kind the underside a part of the record in column 2.
USA
UK
Japan
New Zealand
Australia
Mexico
Belgium
The next code types the record beneath the highest international locations in column 2. As a result of we all know what number of prime international locations there are, the vary begins two rows beneath that worth – to take into consideration the header row.
Set rng = Vary("b" & ctItems + 2 & ":" & ActiveCell.Finish(xlDown).Deal with)
rng.Kind Key1:=Vary("b1"), order1:=xlAscending
The code produces a closing outcome wanting like this:
USA
UK
Japan
Australia
Belgium
Mexico
New Zealand
One space for growth is likely to be to rearrange the highest international locations in a sure order. It might be straightforward sufficient to exhausting code an answer, however it’s good follow to have a scalable resolution; for instance it is likely to be a listing of consumers and you’ll want to spotlight your prime 100 purchasers.
Abstract
This brief VBA code offers an answer to an issue not readily solvable through the use of the usual Excel instruments. It is the kind of state of affairs VBA builders usually face and a very good candidate for saving in a useful location for future reference.