Implicit functions are cool, but SLOW in VB Excel.

gistfile1.vb

Public Sub ExtrapolateInput()

Dim xCell As Range
Dim i As Range
Dim country As Integer

CurrentPath = ActiveWorkbook.Path


' Extend forward last Value and backwards first Value

Sheets("Input").Select       '  Moving focus there.
Set xCell = Sheets("Input").Range("A1")

For country = FirstCountryRow To LastCountryRow
LogInfo "Extrapolation for " & xCell.Cells(country, FirstYearCol - 1)

xCell.Cells(country, LastYearCol).Select
    'Extend Last Value forward
    Extrapola xCell.Cells(country, LastYearCol), -1
    
    'Extend First Value backwards
    Extrapola xCell.Cells(country, FirstYearCol), 1


Next country
LogInfo "Input Sheet Ready"
                    
End Sub

Public Sub Extrapola(xCell As Range, Dir As Integer)

'Fill current cell with the one on the side.
Dim xSide As Range

If Dir = "-1" Then If xCell.Column <= FirstYearCol Then xCell.Value = "#N/A"
If Dir = "1" Then If xCell.Column >= LastYearCol Then xCell.Value = "#N/A"

If IsError(xCell.Value) Then
    'Catch error when Cells has text. Text will be extrapolated
    Exit Sub
End If

If xCell.Value = "" Then
    'Needs Extrapolation
     xCell.Font.ColorIndex = 4 + Dir   'Red for forward, blue for backwards.
     xCell.Font.Bold = True
    Set xSide = xCell.Offset(0, Dir)
    
    If IsError(xSide.Value) Then
    'Catch error when Cells has text. Text will be removed
    xSide.Value = ""
    End If
    
    If xSide.Value = "" Then
       Call Extrapola(xSide, Dir)   'Recursively find the last data.
    End If

    xCell.Value = xSide.Value

End If

End Sub