【VBA】結合されたセルを配列を使って高速に別ブックとして保存する方法

Option Explicit
Sub SaveCombinedCellsToNewWorkbook()
    Dim ws As Worksheet, wbSource As Workbook, wbTarget As Workbook
    Dim lastRow As Long, i As Long
    Dim arrData() As Variant

    ' ソースワークブックとシートを指定
    Set wbSource = ThisWorkbook
    Set ws = wbSource.Sheets("Sheet1")

    ' 最終行を取得
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' データ配列の初期化
    ReDim arrData(1 To lastRow - 1, 1 To 2)

    ' 配列にデータを格納
    For i = 2 To lastRow
        If ws.Range("A" & i).MergeCells Then
            arrData(i - 1, 1) = ws.Range("A" & i).Value
            arrData(i - 1, 2) = ws.Range("B" & i).Value
        End If
    Next i

    ' 新しいワークブックを作成
    Set wbTarget = Workbooks.Add
    With wbTarget.Sheets(1)
        .Cells(1, 1).Resize(lastRow - 1, 2) = arrData
    End With

    ' データの保存と画面更新を停止
    Application.ScreenUpdating = False
    wbTarget.SaveAs Filename:="C:\Users\User\Documents\NewWorkbook.xlsx"
    wbTarget.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub