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

はじめに

前回まで、Excel VBAの配列の種類について説明しました。

lbibouroku.hatenablog.com

今回からは実際にセル範囲を配列に格納する方法を説明します。

セルの範囲を配列に格納するときの注意点

変数をVariant型で宣言しなければいけません。

また、Variant型には「異なるデータ型でも一緒に配列に格納できる」というの特性があります。

静的配列でセル範囲を指定して配列を取得する

今回はこのサンプルデータを使用します。この表は619行目までデータが記入されています。

f:id:lbibouroku:20210823120740p:plain

1次元配列の場合

では、サンプルデータの1行目の値を、配列に格納します。

Sub dataArray1_1()
    
    Dim sampleDataArr As Variant
    sampleDataArr = Sheet1.Range("A1:E1").Value
     
End Sub

配列用の変数をVariant型で宣言して、配列に格納したいセル範囲を指定します。

また、Columns.countを使って一番右の列番号を取得して、セルの範囲指定をすることも可能です。

Sub dataArray1_2()

    Dim lastColumns As Long
    lastColumns = Sheet1.Cells(1, Columns.count).End(xlToLeft).Column
    
    Dim sampleDataArr As Variant
    With Sheet1
        sampleDataArr = Sheet1.Range(Cells(1, 1), Cells(1, lastColumns)).Value
    End With
     
End Sub

ローカルウィンドウで配列の中身を確認すると、どちらの書き方でも同じ結果になりました。

f:id:lbibouroku:20210823120804p:plain

2次元配列の場合

コードの書き方は1次元配列と同じです。このデータは619行目まであるので、セルE619までを指定します。

Sub dataArray1_3()

    Dim sampleDataArr As Variant
    sampleDataArr = Sheet1.Range("A1:E619").Value
     
End Sub

また、CurrentRegionプロパティを使って指定したセルを含む、セルの範囲指定が可能です。

Sub dataArray1_4()

    Dim sampleDataArr As Variant
    sampleDataArr = Sheet1.Cells(1, 1).CurrentRegion.Value
   
End Sub

ただし、CurrentRegionプロパティを使う場合は、表の途中に空白行がないことが前提となりますのでご注意ください。

配列の値をワークシートへ転記する

セルの値をほかのワークシートに転記したい場合は、以下の書き方で転記できます。

ただし、転記されるのは「配列の値」のみなので、罫線などの書式設定は転記されません。その点に注意してください。

Sub dataArray1_5()
    
    Dim sampleData As Variant
    sampleData = Sheet1.Range("A1:E1").Value
    
    Sheet2.Range("A1:E1") = sampleData
     
End Sub