－ちょっと使えるネタを紹介－
Excel VBAで、セルの値を配列へ格納する方法と、配列をセルに入力する方法について、ご紹介します。配列にセルの値を格納するのは、セル範囲を指定して変数に入力するとできます。配列をセルに入力する際には、Resizeを使うと便利です。配列を使うとVBAを高速化できますので、マスターしていきましょう。
この記事では、セルの値を配列に格納する方法と、配列をセルに貼り付ける方法について、ご紹介します。
セルの値を配列に格納するのは、Rangeでセル範囲を指定して、変数に入力するとできます。
セルの値を取得する際は、CurrentRegionを使うと便利です。
配列をセルに貼り付ける際は、Resizeを使ってセル範囲を自動調整すると、便利です。
配列を使って、セルに一括で入力すると、VBAを高速化できますので、マスターしていきましょう。
では、配列への格納とセルに貼り付ける方法について、解説してきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
VBAでセル範囲を、配列へ格納する方法をご紹介します。
セル範囲を配列へ格納する方法は、Rangeでセル範囲を指定して、変数に入力するという感じです。
4行1列のセル範囲をRangeを使って、配列に格納するVBAコードです。
Sub TEST1()
Dim A
'セル範囲を配列に格納
A = Range("A1:A4")
End Sub
配列へ格納するセル範囲です。
では、VBAコードを実行してみます。
「a」という変数に、セル範囲の値を格納することができています。
配列の値は、VBAの実行を中断して確認しています。
配列の大きさは、(1 to 4, 1 to 1)となっています。
こんな感じで、セル範囲の値を配列へ格納すると、1行1列からスタートする2次元配列となります。
横長のセル範囲で、1行4列のセル範囲を配列へ格納してみます。
1行4列のセル範囲を配列へ格納するVBAコードです。
Sub TEST2()
Dim A
'セル範囲を配列に格納
A = Range("A1:D1")
End Sub
1行4列の値を用意しました。
実行してみます。
1行4列の値を配列へ格納できました。
配列の大きさが、(1 to 1, 1 to 4)となっています。
セル範囲の値を、配列へ格納する際は、「CurrentRegion」を使うと便利です。
例えば、A1:C3のセル範囲を、配列に保存する際は、次のようなVBAコードとなりますよね。
Sub TEST3()
Dim A
'セル範囲を配列に格納
A = Range("A1:C3")
End Sub
3行3列の値を用意しました。
実行します。
配列へ格納できました。
ただ、Range("A1:C3")みたいに、セル範囲を指定しないといけないのが、面倒です。
この場合に、「CurrentRegion」を使うとかなり便利です。
セル範囲を、CurrentRegionを使って配列に値を格納するVBAコードです。
Sub TEST4()
Dim A
'表の範囲を配列に格納
A = Range("A1").CurrentRegion
End Sub
先ほど使った3行3列の値で、VBAを実行してみます。
実行してみます。
CurrentRegionを使って、セル範囲を配列に格納できました。
こっちの方が、便利です。セル範囲が大きくなっても同じVBAコードで、セル範囲の値を取得できます。
1次元配列をセル範囲指定して、セルに貼り付ける方法について、ご紹介します。
配列を使えば、値を一括でセルに入力することができるので、高速化できます。
1次元配列を、横方向に貼り付けるVBAコードです。
Sub TEST5()
'1次元配列を作成
Dim A
ReDim A(3)
A(0) = 1
A(1) = 2
A(2) = 3
A(3) = 4
'配列を横方向に貼り付け
Range("A1:D1") = A
End Sub
配列a(3)を、セルに貼り付けます。
配列を横方向に、貼り付けることができました。
特に問題なく、貼り付けることができました。
次に1次元配列を、縦方向に貼り付けてみます。
先に失敗例から書いておきます。
Sub TEST6()
'1次元配列を作成
Dim A
ReDim A(3)
A(0) = 1
A(1) = 2
A(2) = 3
A(3) = 4
'縦方向に配列を貼り付け
Range("A1:A4") = A
End Sub
貼り付け先のセル範囲を、「Range("A1:A4")」で縦方向にしています。
実行してみます。
これは、意図しない結果となりました。
縦方向に貼り付ける際は、「Transpose関数」を使う必要があります。
1次元配列を、縦方向に貼り付けるVBAコードは、こちらになります。
Sub TEST7()
'1次元配列を作成
Dim A
ReDim A(3)
A(0) = 1
A(1) = 2
A(2) = 3
A(3) = 4
'配列を縦方向に貼り付け
Range("A1:A4") = WorksheetFunction.Transpose(A)
End Sub
1次元配列を、「Transpose関数」を使って、行と列を反転させています。
では、実行してみます。
1次元配列を、縦方向に貼り付けられました。
こんな感じで、1次元配列を、縦方向に貼り付ける際は、「Transpose関数」で反転させてから、セルに貼り付けます。
1次元配列に限らず、「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を使って、配列を横方向に貼り付け
Range("A1").Resize(1, UBound(A) + 1) = A
End Sub
「Resize」が貼り付け先のセル範囲を、配列の大きさに自動的に調整してくれます。
では、実行してみます。
1次元配列を、横方向に貼り付けることができました。
貼り付け先のセル範囲は、「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を使って、配列を縦方向に貼り付け
Range("A1").Resize(UBound(A) + 1) = WorksheetFunction.Transpose(A)
End Sub
「Resize」が貼り付け先のセル範囲を、配列の大きさに自動的に調整してくれます。
では、実行してみます。
1次元配列を、縦方向に貼り付けることができました。
貼り付け先のセル範囲は、「Resize」で自動的に大きさを調整してくれます。
こんな感じで、「Resize」を使えば、貼り付け先のセル範囲を自動で調整してくれます。
1次元配列を、扱う場面として、「Split」を使う場合があります。
例えば、コンマ区切りで入力された値を、「Split」で分割して、セルに入力するということをやってみます。
VBAコードはこんな感じになります。
Sub TEST10()
Dim A, B
'コンマ区切りの値を取得
A = Range("A1")
'コンマ区切りで分割
B = Split(A, ",")
'配列をセルに貼り付け
Range("B1").Resize(UBound(B) + 1) = WorksheetFunction.Transpose(B)
End Sub
セルにコンマ区切りの値を用意しました。
実行してみます。
コンマ区切りで分割して、配列をセルに入力することができました。
「Split」を使って分割した結果は、返り値が配列となりますので、配列をセルに貼り付ける方法が必要となります。
「Resize」を使えば、配列の大きさが変わっても、同じVBAコードで配列をセルに貼り付けることができます。
コンマ区切りの値を増やしてみました。
先ほどと同じVBAコードを実行してみます。
コンマ区切りの値を分割して、セルに入力することができました。
こんな感じで、「Resize」を使えば、VBAコードを変更することなく、貼り付ける先のセル範囲を自動で調整してくれます。
次は、2次元配列をセル範囲を指定して、セルに貼り付けてみます。
2次元配列をセルに貼り付けるVBAコードです。
Sub TEST11()
Dim A
'表を配列に格納
A = Range("A1").CurrentRegion
'配列をセルに貼り付け
Range("E1:G3") = A
End Sub
手順としては、
という流れです。
次の値を用意しました。
この表を、配列に格納して、別のセルに貼り付けてみます。
実行してみます。
2次元配列を、セルに貼り付けることができました。
ただ、面倒なのが、配列を貼り付けるセル範囲を「Range("E1:G3")」という感じで、指定しないといけないことです。
配列の大きさが変わると、そのたびにVBAコードを変更しないといけないので、大変です。
ここで、使えるのが、「Resize関数」です。
次でちょっとやってみます。
2次元配列を、「Resize」を使ってセルに貼り付けるVBAコードです。
Resizeを使うと、配列の貼り付けが簡単になります。
Sub TEST12()
Dim A
'表を配列に格納
A = Range("A1").CurrentRegion
'Resizeを使って、配列をセルに貼り付け
Range("E1").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub
手順は、先ほどと同じで、
という流れです。
ポイントは、貼り付け先のセル範囲を、「Resize」で自動的に調整しているところです。
では、先ほどと同じ表を使って実行してみます。
実行します。
2次元配列を、セルに貼り付けることができました。
セルの値を変更して、同じVBAコードを実行してみます。
同じVBAコードを実行してみます。
同じように、2次元配列を、セルに貼り付けることができました。
こんな感じで、「Resize」を使えば、VBAコードを変更することなく、配列をセルに貼り付けることができます。
配列をセルに貼り付ける際は、「Resize」を使うと便利です。
この記事では、セルの値を配列に格納する方法と、配列をセルに貼り付ける方法について、ご紹介しました。
セルの値を配列に格納するのは、Rangeでセル範囲を指定して、変数に入力するとできます。
セルの値を取得する際は、CurrentRegionを使うと便利です。
配列をセルに貼り付ける際は、Resizeを使ってセル範囲を自動調整すると、便利です。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する