VBA - Find Last End Value

[Admin]

Hi

try this

Option Explicit

Sub kTest()
    
    Dim r   As Range
    
    Set r = Range("a1").CurrentRegion
    
    With r
        With r.Columns(1).Offset(, r.Columns.Count)
            .FormulaR1C1 = "=iferror(lookup(2,1/(" & r.Columns(2).Address(, , -4150) & "=rc[-1])," & r.Columns(3).Address(, , -4150) & "),rc[-1])"
            .Value = .Value
            .Cells(1) = "Last"
        End With
    End With
    
End Sub