VBA初心者です。

こんな感じのことでしょうか。

Option Explicit

Sub AutoRank()
    Dim shopName As String
    shopName = Range("A2").Value
    
    Dim startRow As Long
    startRow = 2
    
    Dim rr As Range
    Dim r As Long
    For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Cells(r, "A").Value <> shopName Then
            With Cells(startRow, "D").Resize(r - startRow)
                .FormulaR1C1 = "=RANK(RC[-1],R" & startRow & "C3:R" & r - 1 & "C3,0)" '//  昇順は最後の 0 を 1 に
                .Value = .Value   '// 数式を値に変更
            End With
            shopName = Cells(r, "A").Value
            startRow = r
        End If
    Next
End Sub