大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、セルの値を配列へ格納する方法と、配列をセルに入力する方法について、ご紹介します。配列にセルの値を格納するのは、セル範囲を指定して変数に入力するとできます。配列をセルに入力する際には、Resizeを使うと便利です。配列を使うとVBAを高速化できますので、マスターしていきましょう。

はじめに

この記事では、セルの値を配列に格納する方法と、配列をセルに貼り付ける方法について、ご紹介します。

セルの値を配列に格納するのは、Rangeでセル範囲を指定して、変数に入力するとできます。

セルの値を取得する際は、CurrentRegionを使うと便利です。

配列をセルに貼り付ける際は、Resizeを使ってセル範囲を自動調整すると、便利です。

配列を使って、セルに一括で入力すると、VBAを高速化できますので、マスターしていきましょう。

では、配列への格納とセルに貼り付ける方法について、解説してきます。

この記事で紹介すること

  • セルの値を配列に格納する方法
  • 配列をセルに貼り付ける方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAでセル範囲を配列へ格納

VBAでセル範囲を、配列へ格納する方法をご紹介します。

Rangeを使って配列へ格納

セル範囲を配列へ格納する方法は、Rangeでセル範囲を指定して、変数に入力するという感じです。

4行1列のセル範囲をRangeを使って、配列に格納するVBAコードです。

Sub TEST1()
    
    'セル範囲を配列に格納
    a = ActiveSheet.Range("A1:A4")
    
End Sub

配列へ格納するセル範囲です。

配列に格納するセル範囲

配列へ格納するセル範囲

では、VBAコードを実行してみます。

配列に格納

セル範囲を配列に格納した結果

「a」という変数に、セル範囲の値を格納することができています。

配列の値は、VBAの実行を中断して確認しています。

配列の大きさは、(1 to 4, 1 to 1)となっています。

こんな感じで、セル範囲の値を配列へ格納すると、1行1列からスタートする2次元配列となります。

1行4列のセル範囲を配列へ格納

横長のセル範囲で、1行4列のセル範囲を配列へ格納してみます。

1行4列のセル範囲を配列へ格納するVBAコードです。

Sub TEST2()
    
    'セル範囲を配列に格納
    a = ActiveSheet.Range("A1:D1")
    
End Sub

1行4列の値を用意しました。

1行4列の値

1行4列の値を用意

実行してみます。

配列に格納

1行4列の値を配列へ格納した結果

1行4列の値を配列へ格納できました。

配列の大きさが、(1 to 1, 1 to 4)となっています。

CurrentRegionを使って配列へ格納

セル範囲の値を、配列へ格納する際は、「CurrentRegion」を使うと便利です。

CurrenRegionを使わない場合

例えば、A1:C3のセル範囲を、配列に保存する際は、次のようなVBAコードとなりますよね。

Sub TEST3()
    
    'セル範囲を配列に格納
    a = ActiveSheet.Range("A1:C3")
    
End Sub

3行3列の値を用意しました。

3行3列の値を用意

実行します。

3行3列の値を配列へ格納した結果

配列へ格納できました。

ただ、Range("A1:C3")みたいに、セル範囲を指定しないといけないのが、面倒です。

この場合に、「CurrentRegion」を使うとかなり便利です。

CurrentRegionを使った場合

セル範囲を、CurrentRegionを使って配列に値を格納するVBAコードです。

Sub TEST4()
    
    '表の範囲を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
End Sub

先ほど使った3行3列の値で、VBAを実行してみます。

3行3列の値

実行してみます。

CurrentRegionでセル範囲を配列に格納した結果

CurrentRegionを使って、セル範囲を配列に格納できました。

こっちの方が、便利です。セル範囲が大きくなっても同じVBAコードで、セル範囲の値を取得できます。

VBAで1次元配列を範囲指定してセルに貼り付け

1次元配列をセル範囲指定して、セルに貼り付ける方法について、ご紹介します。

配列を使えば、値を一括でセルに入力することができるので、高速化できます。

横方向に貼り付け

1次元配列を、横方向に貼り付けるVBAコードです。

Sub TEST5()
    
    '1次元配列を作成
    Dim a
    ReDim a(3)
    a(0) = 1
    a(1) = 2
    a(2) = 3
    a(3) = 4
    
    '配列を横方向に貼り付け
    ActiveSheet.Range("A1:D1") = a
    
End Sub

配列a(3)を、セルに貼り付けます。

1次元配列を横方向に貼り付け

配列を横方向に貼り付けた結果

配列を横方向に、貼り付けることができました。

特に問題なく、貼り付けることができました。

縦方向に貼り付け

次に1次元配列を、縦方向に貼り付けてみます。

失敗列

先に失敗例から書いておきます。

Sub TEST6()
    
    '1次元配列を作成
    Dim a
    ReDim a(3)
    a(0) = 1
    a(1) = 2
    a(2) = 3
    a(3) = 4
    
    '縦方向に配列を貼り付け
    ActiveSheet.Range("A1:A4") = a
    
End Sub

貼り付け先のセル範囲を、「Range("A1:A4")」で縦方向にしています。

実行してみます。

1次元配列を縦方向に貼り付けた結果

これは、意図しない結果となりました。

Transpose関数を使う

縦方向に貼り付ける際は、「Transpose関数」を使う必要があります。

1次元配列を、縦方向に貼り付けるVBAコードは、こちらになります。

Sub TEST7()
    
    '1次元配列を作成
    Dim a
    ReDim a(3)
    a(0) = 1
    a(1) = 2
    a(2) = 3
    a(3) = 4
    
    '配列を縦方向に貼り付け
    ActiveSheet.Range("A1:A4") = WorksheetFunction.Transpose(a)
    
End Sub

1次元配列を、「Transpose関数」を使って、行と列を反転させています。

では、実行してみます。

1次元配列をTranspose関数を使って、縦方向に貼り付けた結果

1次元配列を、縦方向に貼り付けられました。

こんな感じで、1次元配列を、縦方向に貼り付ける際は、「Transpose関数」で反転させてから、セルに貼り付けます。

Resizeを使って貼り付け

1次元配列に限らず、「Resize」を使ってセル範囲を指定して、配列を貼り付けると便利です。

「Resize」で横方向に貼り付け

1次元配列を、「Resize」を使って、横方向に貼り付けるVBAコードです。

Sub TEST8()
    
    '1次元配列を作成
    Dim a
    ReDim a(3)
    a(0) = 1
    a(1) = 2
    a(2) = 3
    a(3) = 4
    
    'Resizeを使って、配列を横方向に貼り付け
    ActiveSheet.Range("A1").Resize(1, UBound(a) + 1) = a
    
End Sub

「Resize」が貼り付け先のセル範囲を、配列の大きさに自動的に調整してくれます。

では、実行してみます。

1次元配列を「Resize」を使って、横方向に貼り付けした結果

1次元配列を、横方向に貼り付けることができました。

貼り付け先のセル範囲は、「Resize」で自動的に大きさを調整してくれます。

「Resize」で縦方向に貼り付け

1次元配列を、「Resize」を使って、縦方向に貼り付けるVBAコードです。

Sub TEST9()
    
    '1次元配列を作成
    Dim a
    ReDim a(3)
    a(0) = 1
    a(1) = 2
    a(2) = 3
    a(3) = 4
    
    'Resizeを使って、配列を縦方向に貼り付け
    ActiveSheet.Range("A1").Resize(UBound(a) + 1) = WorksheetFunction.Transpose(a)
    
End Sub

「Resize」が貼り付け先のセル範囲を、配列の大きさに自動的に調整してくれます。

では、実行してみます。

1次元配列を「Resize」を使って、縦方向に貼り付けした結果

1次元配列を、縦方向に貼り付けることができました。

貼り付け先のセル範囲は、「Resize」で自動的に大きさを調整してくれます。

こんな感じで、「Resize」を使えば、貼り付け先のセル範囲を自動で調整してくれます。

Splitで1次元配列に格納して貼り付け

1次元配列を、扱う場面として、「Split」を使う場合があります。

Splitで分割して1次元配列をセルに貼り付け

例えば、コンマ区切りで入力された値を、「Split」で分割して、セルに入力するということをやってみます。

VBAコードはこんな感じになります。

Sub TEST10()
    
    'コンマ区切りの値を取得
    a = ActiveSheet.Range("A1")
    'コンマ区切りで分割
    b = Split(a, ",")
    
    '配列をセルに貼り付け
    ActiveSheet.Range("B1").Resize(UBound(b) + 1) = WorksheetFunction.Transpose(b)
    
End Sub

セルにコンマ区切りの値を用意しました。

セルにコンマ区切りの値を用意

実行してみます。

コンマ区切りで分割して配列をセルに入力した結果

コンマ区切りで分割して、配列をセルに入力することができました。

「Split」を使って分割した結果は、返り値が配列となりますので、配列をセルに貼り付ける方法が必要となります。

Resizeを使うと便利

「Resize」を使えば、配列の大きさが変わっても、同じVBAコードで配列をセルに貼り付けることができます。

コンマ区切りの値を増やしてみました。

コンマ区切りの値を増やしてみた

先ほどと同じVBAコードを実行してみます。

コンマ区切りの値を分割してセルに入力した結果

コンマ区切りの値を分割して、セルに入力することができました。

こんな感じで、「Resize」を使えば、VBAコードを変更することなく、貼り付ける先のセル範囲を自動で調整してくれます。

VBAで2次元配列を範囲指定してセルに貼り付け

次は、2次元配列をセル範囲を指定して、セルに貼り付けてみます。

セルに貼り付け

2次元配列をセルに貼り付けるVBAコードです。

Sub TEST11()
    
    '表を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    '配列をセルに貼り付け
    ActiveSheet.Range("E1:G3") = a
    
End Sub

手順としては、

  • ①セルから値を取得して配列に格納
  • ②2次元配列をセルに貼り付け

という流れです。

次の値を用意しました。

用意した表

セルに値を入力

この表を、配列に格納して、別のセルに貼り付けてみます。

実行してみます。

2次元配列をセルに貼り付け

2次元配列をセルに貼り付けた結果

2次元配列を、セルに貼り付けることができました。

ただ、面倒なのが、配列を貼り付けるセル範囲を「Range("E1:G3")」という感じで、指定しないといけないことです。

配列の大きさが変わると、そのたびにVBAコードを変更しないといけないので、大変です。

ここで、使えるのが、「Resize関数」です。

次でちょっとやってみます。

Resizeを使ってセル貼り付け

2次元配列を、「Resize」を使ってセルに貼り付けるVBAコードです。

Resizeを使って配列をセルに貼り付け

Resizeを使うと、配列の貼り付けが簡単になります。

Sub TEST12()
    
    '表を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    'Resizeを使って、配列をセルに貼り付け
    ActiveSheet.Range("E1").Resize(UBound(a, 1), UBound(a, 2)) = a
    
End Sub

手順は、先ほどと同じで、

  • ①セルから値を取得して配列に格納
  • ②2次元配列をセルに貼り付け

という流れです。

ポイントは、貼り付け先のセル範囲を、「Resize」で自動的に調整しているところです。

では、先ほどと同じ表を使って実行してみます。

セルに値を入力

実行します。

Resizeを使って、2次元配列をセルに貼り付けた結果

2次元配列を、セルに貼り付けることができました。

表の大きさを変更してみる

セルの値を変更して、同じVBAコードを実行してみます。

セルに入力した値を変更

同じVBAコードを実行してみます。

Resizeを使って、2次元配列をセルに貼り付けた結果

同じように、2次元配列を、セルに貼り付けることができました。

こんな感じで、「Resize」を使えば、VBAコードを変更することなく、配列をセルに貼り付けることができます。

配列をセルに貼り付ける際は、「Resize」を使うと便利です。

おわりに

この記事では、セルの値を配列に格納する方法と、配列をセルに貼り付ける方法について、ご紹介しました。

セルの値を配列に格納するのは、Rangeでセル範囲を指定して、変数に入力するとできます。

セルの値を取得する際は、CurrentRegionを使うと便利です。

配列をセルに貼り付ける際は、Resizeを使ってセル範囲を自動調整すると、便利です。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す