VBA: How to Rank a List of Values

You can use the following basic syntax to rank a list of values in Excel using VBA:

Sub RankValues()
    Dim i As Integer

    For i = 2 To 11
    Range("C" & i) = WorksheetFunction.Rank(Range("B" & i), Range("B2:B11"), 0)
    Next i
End Sub

This particular example ranks the values in cells B2:B11 and outputs the ranks in cells C2:C11.

The last argument of of 0 specifies that we should rank the values in ascending order (the largest value receives a rank of 1, the second largest value receives a rank of 2, etc.).

To rank the values in descending order, simply change the 0 to 1.

The following example shows how to use this syntax in practice.

Example: How to Rank Values Using VBA

Suppose we have the following list of basketball players along with their points scored:

Suppose we would like to calculate the rank of each value in the points column.

We can create the following macro to do so:

Sub RankValues()
    Dim i As Integer

    For i = 2 To 11
    Range("C" & i) = WorksheetFunction.Rank(Range("B" & i), Range("B2:B11"), 0)
    Next i
End Sub

When we run this macro, we receive the following output:

The rank of each value in the points column is displayed in column C.

For example:

Player H with 41 points has the highest points value, so they receive a rank of 1.
Player C with 40 points has the second highest points value, so they receive a rank of 2.

And so on.

To instead rank the values in the points column in descending order, we can change the last argument in the Rank method from 0 to 1:

Sub RankValues()
    Dim i As Integer

    For i = 2 To 11
    Range("C" & i) = WorksheetFunction.Rank(Range("B" & i), Range("B2:B11"), 1)
    Next i
End Sub

When we run this macro, we receive the following output:

The rank of each value in the points column is displayed in column C.

For example:

Player I with 11 points has the lowest points value, so they receive a rank of 1.
Player E with 13 points has the second lowest points value, so they receive a rank of 2.

And so on.

Note: You can find the complete documentation for the VBA Rank method here.