I need help in creating an excel VBA script that will do the following. I know it needs a loop!

My data looks like this

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

I need VBA script in Excel that can start in A1 and paste it into A2, A3, A4 - then realise that Spain is different to England and that need copying 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 any help here would be fantastic thank you-

CodePudding user response：

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

CodePudding user response：

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.