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