Ranking the rows without sorting

[mancubus]

i use below in one of my projects...

Sub vbax_58943_rank_range()
    
    Dim rng As Range
    
    With Worksheets("Sheet1")
        Set rng = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        rng.Offset(, 1).Value = Application.Rank(rng, rng)
    End With
End Sub

Application.Rank(rng, rng) or Application.Rank(rng, rng, 0) ranks in descending order

Application.Rank(rng, rng, 1) ranks in ascending order (third argument, the order, any nonzero value as per MS help)