今回はExcelVBAのCurrentRegionについて解説したいと思います。
最終的には下の動画のようにCurrentRegionを活用して、行ごとにセルの数値をA列にカンマ区切りで結合する方法（可変対応）を紹介します。
CurrentRegionの基本
CurrentRegion（カレントリージョン）は、空白のセルで囲まれた範囲を読み取り、参照するプロパティです。
特長として、Rangeオブジェクトにのみ用意されているプロパティです。
表の範囲の全選択
下記の表を全選択する場合
Range("B1").CurrentRegion.Select
ボタン操作のCtrl+Shift+*と同じ動きをします。
表の範囲を全選択（１行下にシフト）
表の全選択から１行下にシフトする場合
Range("B1").CurrentRegion.Offset(1).Select
Offsetの基本
Offset(移動する行数、移動する列数）となります。
Offset(1)は行方向に1行下に移動します。
Offset(,1)は列方向に１列右に移動します。
Offset(1,1)は行方向に1行下、列方向に１列右に移動します。
Offset(-1,-1)は行方向に１行上、列方向に１列左に移動します。
CurrentRegionプロパティの特定の行を選択する
CurrentRegionで指定された範囲の特定の行を選択します。
Range("B1").CurrentRegion.Rows(4).Select
上の画像のように、CurrentRegionで指定された範囲の上から4行目が選択されます。
CurrentRegionプロパティの特定の列を選択する
CurrentRegionで指定された範囲の特定の列を選択します。
Range("B1").CurrentRegion.Columns(5).Select
CurrentRegionプロパティの表の最終行の行番号を取得
MsgBox Range("B4").CurrentRegion.Rows(Range("B4").CurrentRegion.Rows.Count).row
先程のROWS（番号）やCOLUMNS（番号）については、あくまでCurrentRegionで取得された範囲内での上から数えた行番号や左から数えた列番号ですが、こちらのコードの最終行についてはCurrentRegionで取得された表の範囲内ではなく、ワークシートの行番号を取得します。
CurrentRegionで取得された表の範囲内の行数を取得
MsgBox Range("B4").CurrentRegion.Rows.Count
こちらは行数の取得であり、表内の最終行番号になります。
CurrentRegionプロパティの表の最終列の列番号を取得
MsgBox Range("B4").CurrentRegion.Columns(Range("B4").CurrentRegion.Columns.Count).Column
同じく、ワークシートの最終列番号を取得します。
CurrentRegionで取得された表の範囲内の列数を取得
MsgBox Range("B4").CurrentRegion.Columns.Count
こちらは列数の取得であり、表内の最終列番号になります。
CurrentRegionを活用して、行ごとにセルの数値をA列にカンマ区切りで結合パターン１
Sub 結合()
Dim Rng As Range
Dim R As Range
Dim Wd As Variant
Dim Num As Integer
Dim i As Integer
Set Rng = Range("B1").CurrentRegion '表をオブジェクト変数に格納
Num = Rng.Rows(Rng.Rows.Count).row '最終行の取得
Range("A:A").ClearContents
For i = 2 To Num '2行目から最終行までのループ
Wd = ""
For Each R In Rng.Rows(i).Columns '行ごとにカラムコレクションでセルの値を取得
If R <> "" Then
If Wd = "" Then
Wd = R.Value
Else
Wd = Wd & "," & R 'カンマ区切りで値を結合
End If
End If
Next R
Cells(i, 1).Value = Wd '結合した値を貼付
Next i '次の行へ
Range("A1").Select
End Sub
CurrentRegionを活用して、行ごとにセルの数値をA列にカンマ区切りで結合パターン２
こちらについては、CurrentRegionプロパティを活用しますが、パターン１と違う点として下記があります。
- 二重For文を使っている点
- データを配列に格納して、Join関数で結合している点
Sub 結合_2()
Dim Wd() As String
Dim Maxcol As Integer
Dim Maxrow As Integer
Dim Rng As Range
Dim i As Integer
Dim j As Integer
Dim x As Integer
Set Rng = ThisWorkbook.Sheets("sheet1").Range("B1").CurrentRegion '表をオブジェクト変数に格納
Maxrow = Rng.Rows(Rng.Rows.Count).row '最終行数の取得
Maxcol = Rng.Columns(Rng.Columns.Count).Column '最終列数の取得
For i = 2 To Maxrow '最終行数
x = 0
For j = 2 To Maxcol '最終列数
If Cells(i, j).Value <> "" Then
ReDim Preserve Wd(x) '配列
Wd(x) = Cells(i, j).Value '配列に格納
x = x + 1
End If
Next j
Cells(i, 1) = Join(Wd, ",") '配列変数を結合してセルに代入
Next i
End Sub
動的配列(ReDim)
静的配列は変数宣言の時点で要素数を決めました。
例：Dim Wd（2）As String
上記例の場合、要素数は０、１、２です。
しかし、動的配列の場合は配列宣言時には要素数は指定せず、空の（）にします。そして、実行途中で要素数を決めます。
今回のコードの場合、For文の中で要素数が決定されていくので、宣言時点では要素数は決めれません。
変数宣言時点
Dim Wd() As String
実行途中で要素数が決定
ReDim Preserve Wd(x) '配列
この場合、実行途中で要素数X＝0が決定されます。そして、ループする毎にX＝X+１で要素数が増加していきます。
Preserve
Preserveを付けることで、それまで配列に入っていた値は保持されます。逆にPreserveが無くRedimのみの場合それまで配列に入っていた値は失われます。
Preserveが無い場合
１が消えてしまいます。
Preserveがある場合
１は残ります。
・筆者オススメのVBAの本はこちら↓
まとめ
CurrentRegionを使用した表の選択や行、列の選択、その活用形について紹介しました。CurrentRegion自体はVBAでコードを書くにあたって使いやすいプロパティなので、是非参考にしてみてください！
次回も皆さんに有益なエクセル情報を紹介します！お楽しみに！
コメント