VBA 結合されたセルの解除と解除した全セルに値を設定

全カラムのマージを解除

'全カラムのマージを解除
Private Sub UnMargeAllCell(ByRef wsWorkSheet As Worksheet)
    
    Dim rngRange                As Range
    Dim strMargeAreaAddress     As String
    
    For Each rngRange In wsWorkSheet.UsedRange
        If rngRange.MergeCells = True Then
            strMargeAreaAddress = rngRange.MergeArea.Address
            rngRange.MergeArea.UnMerge
            wsWorkSheet.Range(strMargeAreaAddress).Value = rngRange.MergeArea.Resize(1, 1).Value
        End If
    Next
    
End Sub

'範囲を指定して、セルの結合を解除（こっちの方がスピード的に現実的かな）
Private Sub UnMargeAllCell(ByRef wsWorkSheet As Worksheet)
    
    Dim strMargeAreaAddress     As String
    Dim lngLoopRow              As Long
    Dim lngLoopCol              As Long
    
    For lngLoopRow = 1 To 3
        For lngLoopCol = 1 To 200
            DoEvents
            Call dispInformationAtStatusBar("UnMerge " & lngLoopRow & "-" & lngLoopCol)
            If wsWorkSheet.Cells(lngLoopRow, lngLoopCol).MergeCells = True Then
                '解除前のアドレスを保持
                strMargeAreaAddress = wsWorkSheet.Cells(lngLoopRow, lngLoopCol).MergeArea.Address
                DoEvents
                'マージを解除
                wsWorkSheet.Cells(lngLoopRow, lngLoopCol).MergeArea.UnMerge
                DoEvents
                '解除したセルに値をコピー
                wsWorkSheet.Range(strMargeAreaAddress).Value = wsWorkSheet.Cells(lngLoopRow, lngLoopCol).MergeArea.Resize(1, 1).Value
            End If
        Next lngLoopCol
    Next lngLoopRow
    
End Sub

最終行と最終列

.Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(1, .Columns.Count).End(xlToLeft).Column