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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Cells with Specific Text

VBA: How to Count Number of Rows in Range

VBA: How to Write COUNTIF and COUNTIFS Functions