Home » Word Press » How To Sort An Excel List Conditionally Using VBA Code

How To Sort An Excel List Conditionally Using VBA Code

It’s simple sufficient to type a listing the usage of Excel’s usual sorting equipment or making use of a serve as without delay in VBA code. But it is a little tougher to type a listing the place you wish to have to use your individual standards.

An Example Of Conditional Sorting

A standard state of affairs may well be to type alphabetically the next record of nations, however all the time have the large areas like america, UK and Japan on the best of the record.

 

Country
New Zealand
Australia
USA
Mexico
Belgium
UK
Japan

We’ll create a brand new record the usage of some easy VBA code which you are able to adapt to satisfy your individual wishes.

Organizing The Code

One option to this drawback is to reorganize the record so the highest nations are on the best after which type the 2 spaces of the record one by one.

First, we’re going to outline the names and selection of nations we wish to seem on the best of the record.


bestItems = ",USA,UK,Japan,"
ctItems = UBound(Split(bestItems, ",")) - 1

Next, we will be able to choose the record and set a counter for the selection of “top” nations and “others”.


Set rng = ActiveCell.CurrentRegion
best = 1
others = 1

Now we are able to split out the record into the highest nations and others which we’re going to do through shifting each and every nation into a brand new record along the outdated one. Don’t disregard we wish to forget about the header row.


For x = 2 To rng.Rows.Count

If the present cellular worth is among the best nations then we’re going to transfer the worth to the highest of a brand new record, and if no longer we’re going to transfer it to the ground of the brand new record.


If InStr(bestItems, "," & rng.Rows(x) & ",") Then
best = best + 1
Cells(best, 2) = rng.Rows(x)
Else
others = others + 1
Cells(others + ctItems, 2) = rng.Rows(x)
End If
Next

Our record is now reorganized within the following manner, and we simply wish to type the ground a part of the record in column 2.


USA
UK
Japan
New Zealand
Australia
Mexico
Belgium

The following code types the record beneath the highest nations in column 2. Because we understand how many best nations there are, the variability starts two rows beneath that worth – to remember the header row.


Set rng = Range("b" & ctItems + 2 & ":" & ActiveCell.End(xlDown).Address)
rng.Sort Key1:=Range("b1"), order1:=xlAscending

The code produces a last consequence taking a look like this:


USA
UK
Japan
Australia
Belgium
Mexico
New Zealand

One space for construction may well be to prepare the highest nations in a definite order. It can be simple sufficient to exhausting code an answer, however it’s just right apply to have a scalable answer; for instance it may well be a listing of consumers and you wish to have to focus on your best 100 clients.

Summary

This brief VBA code supplies a option to an issue no longer readily solvable through the usage of the usual Excel equipment. It’s the kind of state of affairs VBA builders continuously face and a just right candidate for saving in a to hand location for long run reference.

Check Also

Tabe   Premium Tabs (Navigation)

tabe – Premium Tabs (Navigation)

tabe – Premium Tabs (Navigation). tabe is a straightforward Tabs Plugin. Based on jQuery and …