Excel VBAを使ってセル範囲を配列に格納するには - 動的配列2 -

はじめに

前回は、セル範囲を動的配列に格納する方法（2次元目のインデックス範囲を変更）について説明しました。

lbibouroku.hatenablog.com

今回はセル範囲を動的配列に格納する方法（1次元目のインデックス範囲を変更）について説明します。

◆目次◆

はじめに
Transpose関数
1次元目のインデックスの範囲の変更
おわりに

Transpose関数

先に今回の肝となる「Transpose関数」を紹介します。

Transpose関数：行と列を入れ替える関数

Excelのワークシート上で、右クリックで貼り付けするときに出てくる「行/列の入れ替え」と同じことができます。

f:id:lbibouroku:20210830210316p:plain

使い方は以下のとおりです。

WorksheetFunction.Transpose(行と列を入れ替えたいセル範囲や配列)

Transpose関数の実例は、次の節で説明します。

1次元目のインデックスの範囲の変更

さて、ここが山場です。

以前の記事で、動的配列について以下のように書きました。

ただし、ReDim Preserveステートメントを使って変更できるのは「配列の最後の次元のみ」ということに注意が必要です。

つまり、「2次元配列において、1次元目のインデックス範囲は変更できない」ということです。

でも、1次元目のインデックス範囲を変更したいことは多々あります。そこで、先ほど紹介したTranspose関数が活躍します！

先に、マクロに記述する手順を説明をします。イメージは以下の画像を参照してください（立方体に書いている文字は、セルの位置を指します）。

変数：lastRowsを宣言し、Sheet1の最下行番号を代入する
配列の変数：sampleDataArrayを宣言する
For Next構文でセルの値を変数：sampleDataArrayへ格納する（わざと行と列を入れ替えて値を格納します、画像を参照してください）
新しい配列の変数：sampleDataArray_2を宣言する
変数：sampleDataArrayを、Transpose関数で行と列を入れ替えて変数：sampleDataArrayArr_2に格納する（画像を参照してください）

つまり、手順の3と5の、計2回「行と列を入れ替え」が発生していることになります。

この流れをコードにすると、以下のとおりです（コメントアウトしている①と②は後ほど説明します）。

Sub dataArray2_3()

    '1. 変数：lastRowsを宣言し、Sheet1の最下行番号を代入する
    Dim lastRows As Long
    lastRows = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    '2. 配列の変数：sampleDataArrayを宣言する
    Dim sampleDataArray As Variant

    '3. For Next構文でセルの値を変数：sampleDataArrayへ格納する（わざと行と列を入れ替えて値を格納する）
    Dim i As Long
    For i = 1 To lastRows
        ReDim Preserve sampleDataArray(1 To 5, 1 To i)
        sampleDataArray(1, i) = Sheet1.Cells(i, 1).Value
        sampleDataArray(2, i) = Sheet1.Cells(i, 2).Value
        sampleDataArray(3, i) = Sheet1.Cells(i, 3).Value
        sampleDataArray(4, i) = Sheet1.Cells(i, 4).Value
        sampleDataArray(5, i) = Sheet1.Cells(i, 5).Value
    Next i
    
'    With Sheet2　'①
'        .Range(.Cells(1, 1), .Cells(UBound(sampleDataArray, 1), UBound(sampleDataArray, 2))).Value = sampleDataArray
'    End With

    '4. 新しい配列の変数：sampleDataArray_2を宣言する
    Dim sampleDataArray_2 As Variant

    '5. 変数：sampleDataArrayを、Transpose関数で行と列を入れ替えて変数：sampleDataArrayArr_2に格納する
    sampleDataArray_2 = WorksheetFunction.Transpose(sampleDataArray)
    
'    With Sheet3　'②
'        .Range(.Cells(1, 1), .Cells(UBound(sampleDataArrayArr_2, 1), UBound(sampleDataArrayArr_2, 2))).Value = sampleDataArrayArr_2
'    End With

End Sub