Copy and Paste to all empty cells below repeat at next non-empty cell

Question

My data looks like this

ROW - Column A
1     England
2
3
4
5     Spain
6
7     Germany 
8

I need to start in A1 and paste into A2, A3, A4 - then realise that Spain is different to England and copy into A6 - then realise that Germany is different and copy that into A8. This is dummy data and the list is very long with lots of blank cells that need populating.

Answer 1

Autofill Column

Option Explicit

Sub AutoFillColumn()
    
    Const sFirst As String = "A1"
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim lCell As Range
    Set lCell = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' empty worksheet
    
    Dim fCell As Range: Set fCell = ws.Range(sFirst)
    Dim fRow As Long: fRow = fCell.Row
    Dim lRow As Long: lRow = lCell.Row
    If Not lRow > fRow Then Exit Sub ' last row not greater than first
    
    Dim rCount As Long: rCount = lRow - fRow + 1
    Dim crg As Range: Set crg = fCell.Resize(rCount)
    Dim cData As Variant: cData = crg.Value
    
    Dim OldValue As Variant
    Dim NewValue As Variant
    Dim r As Long
    
    For r = 1 To rCount
        NewValue = cData(r, 1)
        If IsEmpty(NewValue) Then
            cData(r, 1) = OldValue
        Else
            OldValue = NewValue
        End If
    Next r
    
    crg.Value = cData
    
End Sub

Answer 2

1. Select all entries in the column.
2. Goto Special, blanks.
3. Click inside the formula bar and type =A1 (I believe your first entry is "A1").
4. Press Ctrl+ENTER.