【Excel VBA】シート間のデータ移行処理を高速化（配列処理）

今回は、あるシートのデータを他のシートにデータ移行するようなExcelマクロを作る時の注意点について、解説していきます。

あるシートのデータを別シートの違ったフォーマットにデータを移行するのに、手作業でコピペを繰り返し、面倒な思いをされている方って多いのではないでしょうか。

データ移行は、Excel作業の中でも、よくある処理ですよね。

Excelマクロでデータ移行処理の方法を考えた時、思いつくのが下の2つの方法ではないでしょうか。

データ1件ずつシート間を移行
一度、配列に格納してから別シートへ転記

今回はこれら処理方法についての解説だけでなく、2つの方法でプログラミングした時の比較検証もしてみたいと思います。

どちらの方法が、高速で効率的に処理できるか注目してください。

まずは準備から
1件ずつデータ移行
配列でデータ移行
配列を一度に貼り付け

まずは準備から

今回、時間を許すようでしたら、一緒にプログラミングしてみてほしいのですが、まず、比較検証に使用するデータを用意します。

下のコードを標準モジュールに入力してください。

Public Sub DataList()

    Dim i As Long
    Dim j As Long
    Dim lngData As Long
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    lngData = 1
    
    For j = 1 To 100
        For i = 1 To 1000
            ws.Cells(i, j).Value = lngData
            lngData = lngData + 1
        Next i
    Next j
    
End Sub

このコードは、行、列に対応したカウンタをループさせることで、「Sheet1」という名前のシートに縦1,000行、横100列、10万件の数値データを用意します。

この時、数値データは1ずつ増やしていきます。

行と列を分けて、ループをネストさせているところがポイントですね。

次項から、ExcelVBAを使って、別シートに移行していく方法について、解説していきます。

1件ずつデータ移行

用意した10万件のデータを1件ずつ、別シートに移行してみます。

今回は、10万件のデータを用意したシートと全く同じ並びで別シートにデータを移行するコードを考えてみます。

下のコードを標準モジュールに用意しましょう。

Public Sub DataMove1()

    Dim i As Long
    Dim j As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim t As Double

    t = Timer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

    For j = 1 To 100
        For i = 1 To 1000
            ws2.Cells(i, j).Value = ws1.Cells(i, j).Value
        Next i
    Next j
    
    MsgBox (Timer - t)
    
End Sub

移行元のシートの名前は「Sheet1」、そして移行先のシートの名前は「Sheet2」ですね。

このコードでは、10万件のデータをループさせて、1件ずつデータ移行させています。

ここでも、行と列に分けて、ループをネストさせているところがポイントです。

また、「Timer」オブジェクトを使って、処理速度を計っています。

実際に実行してみてください。

私のPCでは4.5秒ほどで処理が終わりました。

処理速度については、実行するPCのスペックによって違ってくるとは思いますが、以下、他の方法と比較してみます。

配列でデータ移行

10万件全てのデータを、一旦、配列に退避させてから、別シートに配列データを落とし込んでいく方法を考えてみます。

これも、別シートに全く同じ並びでデータを移行させることとします。

下のコードを、標準モジュールに用意してください。

Public Sub DataMove2()

    Dim arrData As Variant
    
    Dim i As Long
    Dim j As Long
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim t As Double
    t = Timer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    arrData = ws1.Cells(1, 1).CurrentRegion.Value
    
    For j = 1 To UBound(arrData, 2)
        For i = 1 To UBound(arrData, 1)
            ws2.Cells(i, j).Value = arrData(i, j)
        Next i
    Next j
    
    MsgBox (Timer - t)
    
End Sub

配列用の変数「arrData」を用意し、「CurrentRegion」を使って、配列にデータを退避しています。

ポイントはこの配列用の変数「arrData」のデータ型が「Variant」であることです。

範囲指定して、一度に配列にデータを格納する場合は、必ずデータ型が「Variant」でなければならないことに注意しましょう。

さらに、範囲指定して一度に配列にデータを格納すると、2次元配列（インデックス（添え字）は「1」から始まります）になることにも注意してください。

行、列、それぞれカウンタを使って、ループをネストさせ、配列データを移行先の別シートに落とし込んでいきます。

処理とあわせて、タイムも計っていますので、実際に実行してみてください。

約3.9秒から4秒でした。

タイムはPCの実行環境によりますが、データを1件ずつ移行させるより、少し高速になったことがわかります。

つまり、データを1件ずつ処理するより、配列を使った方が高速なのです。

配列を一度に貼り付け

同じ方法で10万件のデータを配列に退避させ、それを一度に貼り付けることもできます。

つまり、ループさせて処理するのではなく、貼り付け先を指定して、配列データを「貼り付ける」ことができるのです。

「貼り付ける」と書きましたが、本当にコピペの「貼り付け」のようなイメージです。

下のコードを標準モジュールに用意します。

Public Sub DataMove3()

    Dim arrData As Variant
    
    Dim i As Long
    Dim j As Long
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim t As Double
    t = Timer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    arrData = ws1.Cells(1, 1).CurrentRegion.Value
    
    With ws2
        .Range(.Cells(1, 1).Address & ":" & .Cells(UBound(arrData, 1), UBound(arrData, 2)).Address).Value = arrData
    End With
    
    MsgBox (Timer - t)
    
End Sub

先ほどと同じく、配列用の変数「arrData」に、「CurrentRegion」を使って、配列にデータを格納します。

そして、貼り付ける範囲を「Range」オブジェクトで指定できれば、「=」を使って、配列データを貼り付けることができてしまいます。

この場合、貼り付け範囲を「Range」オブジェクトで指定するのに、ちょっとしたコツが必要ですが、それについては下のリンクで解説していますので、参考にしてください。