# 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.