复制并粘贴到下面的所有空单元格在下一个非空单元格重复

Question

我的数据如下所示

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

我需要从A1开始，粘贴到A2、A3、A4中，然后意识到西班牙与英国不同并复制到A6中，然后意识到德国不同并将其复制到A8中。这是虚拟数据，列表非常长，有许多需要填充的空白单元格。

Answer 1

自动填充列

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