Excel macro to rank score SEO data exports

srContentGapRanking.vb

Sub semrushContentGapRanking()
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim pt As pivottable
    
    ' Set the worksheet where you want to perform the operations
    Set ws = ActiveSheet
    
    ' Find the last row with data in Column A
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Concatenate values from Columns A & G into Column R
    ws.Range("R2:R" & LastRow).Formula = "=A2 & G2"
    ws.Range("R1").Value = "UKWID"
    
    ' Generate percentrank formula for values in Column B into Column S
    ws.Range("S2:S" & LastRow).Formula = "=10-(PERCENTRANK($B$2:$B$" & LastRow & ", B2) * 10)"
    ws.Range("S1").Value = "Position rank"
    
    ' Generate percentrank formula for values in Column D into Column T
    ws.Range("T2:T" & LastRow).Formula = "=PERCENTRANK($D$2:$D$" & LastRow & ", D2) * 10"
    ws.Range("T1").Value = "MSV rank"
    
    ' Sum values of Columns S & T into Column U
    ws.Range("U2:U" & LastRow).Formula = "=S2 + T2"
    ws.Range("U1").Value = "SEO score"
    
    End Sub