空值填充问题

人手操作步骤:

选范围-编辑-定位-空值-光标点到公式编辑栏-输入公式-按着alt+回车,如gif图片

vba代码解决

Sub mergecells_insertvalue()    '自动拆分合并单元格+填充值
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo tuichu
    Dim cell   As Range
    For Each cell In Selection
        If cell.mergecells = True Then
            cell.mergecells = False
        End If
    Next
    Dim i, a   As Integer   '填充上一行值
    i = Selection.Rows.count
    For a = 1 To i
        With ActiveCell
            If .Value = "" Then
                .Offset(-1, 0).Copy
                .PasteSpecial xlPasteAll
            End If
            .Offset(1, 0).Select
        End With
    Next

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
tuichu:
End Sub