Hi,

I already have this code which adds a blank row and counts between them.
and would also like to add a ranking column. (see column E in this example)

RANK betweeen Headers.xlsm

current code
Sub Data sort()
    Dim myAreas As Areas, myArea As Range
    With Sheets("sheet1")
        .Columns(1).Insert
        With .Range("b1", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=if(b1=""ref"",1,""a"")"
            .Value = .Value
            On Error Resume Next
            .SpecialCells(2, 1).EntireRow.Insert
        End With
        Set myAreas = .Columns(1).SpecialCells(2, 2).Areas
        If myAreas Is Nothing Then Exit Sub
        On Error GoTo 0
        For Each myArea In myAreas
            myArea.Offset(, 4).Value = myArea.Rows.Count
        Next
        .Columns(1).Delete
    End With
    Set myAreas = Nothing
End Sub
Thanks in advance