【Excel/VBA】配列をがばっと入れる②～セル範囲

先日配列をがばっと入れるをやりました。

　【Excel/VBA】配列～セル範囲の縦一列をがばっと入れる。または横一列をがばっと入れる

縦一列、横一列だったので、複数行、複数列のセル範囲をがばっと入れた場合の挙動検証を今回はやります。

（１）セル範囲を配列に入れます
（２）配列の中身をイミディエイトウインドウに出してみる。
（３）配列の中身を出力する
（４）配列の中身を縦横入れ替えて出力する
（５）配列の中身を一つずつ取り出す
（６）For Each で配列の中身を一つずつ取り出す
（７）配列に入れるセル範囲が明確でないときの対応
（８）セル範囲をRnage型の変数に入れる

セルA1～E3の範囲を配列に入れます。内容はこんな感じ。

（１）セル範囲を配列に入れます

　Dim arr As Variant
　arr = Range(“A1:E3”)

（２）配列の中身をイミディエイトウインドウに出してみる。

Debug.Print “(1,1)は” & arr(1, 1)
Debug.Print “(1,5)は” & arr(1, 5)
Debug.Print “(3,1)は” & arr(3, 1)
Debug.Print “(3,5)は” & arr(3, 5)

すると、イミディエイトウインドウには

(1,1)は1
(1,5)は5
(3,1)は11
(3,5)は15

とでます。

↓ 配列の中身のイメージ

（３）配列の中身を出力する

配列と同じ大きさのセル範囲に配列の中身をいっぺんに出力するにはこのようにします
Range(“A5:E7”).Value = arr

（４）配列の中身を縦横入れ替えて出力する

縦横入れ替えて配列の中身を出したいときは WorksheetFunction.Transpose を使います。
Range(“A10:C14”).Value = WorksheetFunction.Transpose(arr)

このように出ます。

（５）配列の中身を一つずつ取り出す

ひとつずつとりだしてみました。

Dim i As Long, j As Long
For i = LBound(arr) To UBound(arr)
　For j = LBound(arr, 2) To UBound(arr, 2)
　　Cells(5 + i, j).Value = arr(i, j)
　Next j
Next i

（６）For Each で配列の中身を一つずつ取り出す

　For EachでH列にひとつずつだしてみました。１列ずつ、上から下に取り出されるんですね。
Dim v As Variant
i = 1
For Each v In arr
Cells(i, 8).Value = v
i = i + 1
Next

（７）配列に入れるセル範囲が明確でないときの対応

たとえばセルA１から一続きのセル範囲を配列に入れる場合です。

Range（”A1”）のCurrentregionとして、配列にセル範囲の値を入れています。

Dim arr As Variant
With ActiveSheet
　arr = .Range(“A1”).CurrentRegion.Value
End With

同じ大きさのセル範囲に配列を一気に出力したいとき、縦横の大きさがわからないのでUBOUNDを使って配列の長さを取得します。

Dim r As Long, c As Long
r = UBound(arr, 1) ‘配列縦の大きさ
c = UBound(arr, 2) ‘配列横の大きさ

ｒには縦の大きさ「３」が入ります。ｃには横の大きさ「５」が入ります。

ｒ・ｃを使ってセルA5を起点として配列を一気に出力ます。
With ActiveSheet
　.Range(.Cells(5, 1), .Cells(5 + r – 1, c)).Value = arr
End With

（８）セル範囲をRnage型の変数に入れる

セル範囲をRnage型の変数に入れると、配列と同じようなことができますがちょっと違うところがあります。

Sub test5()
　’範囲をRange型変数に入れる
　Dim rng As Range
　Set rng = Range(“A1:E3”)　　　　　　’①

　Debug.Print “(1,1)は” & rng(1, 1)　　　’②
　Debug.Print “(1,5)は” & rng(1, 5)
　Debug.Print “(3,1)は” & rng(3, 1)
　Debug.Print “(3,5)は” & rng(3, 5)

　Range(“A5:E7”) = rng.Value　　　　　’③
　Range(“A10:C14”).Value = WorksheetFunction.Transpose(rng)　　’④
　Range(“H1:h2”).Value = rng.Columns(3).Value　　’⑤
End Sub

①セル範囲をRange型変数、rngに入れます。

注意するところrange型変数への代入はSetが必要なところです。

Dim rng As Range
Set rng = Range(“A1:E3”)

②rng(1,1)とすると一番左上の値が取り出せます。

ここは配列と同じあつかいができます。

arr(1, 1).Valueと省略せずにValueをつけてもいいかもしれません。

③同じ大きさの範囲にRange型変数の中身を取り出すときは

Range(“A5:E7”) = rng.Value

とします。

配列と違う点は、.Valueがないとだめみたいです。

④縦横入れ替えて出力する場合

Range(“A10:C14”).Value = WorksheetFunction.Transpose(rng)