VBAにおけるCurrentRegionの活用

Excel
スポンサーリンク

今回はExcelVBAのCurrentRegionについて解説したいと思います。

最終的には下の動画のようにCurrentRegionを活用して、行ごとにセルの数値をA列にカンマ区切りで結合する方法(可変対応)を紹介します。

サンプル動画
スポンサーリンク

CurrentRegionの基本

CurrentRegion(カレントリージョン)は、空白のセルで囲まれた範囲を読み取り、参照するプロパティです。

特長として、Rangeオブジェクトにのみ用意されているプロパティです。

表の範囲の全選択

下記の表を全選択する場合


Range("B1").CurrentRegion.Select

ボタン操作のCtrl+Shift+*と同じ動きをします。

範囲の全選択

表の範囲を全選択(1行下にシフト)

表の全選択から1行下にシフトする場合


 Range("B1").CurrentRegion.Offset(1).Select

1行シフト

Offsetの基本

Offset(移動する行数、移動する列数となります。

Offset(1)は行方向に1行下に移動します。

Offset(,1)は列方向に1列右に移動します。

Offset(1,1)は行方向に1行下、列方向に1列右に移動します。

Offset(-1,-1)は行方向に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列にカンマ区切りで結合パターン1


 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列にカンマ区切りで結合パターン2

こちらについては、CurrentRegionプロパティを活用しますが、パターン1と違う点として下記があります。

  1. 二重For文を使っている点
  2. データを配列に格納して、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

上記例の場合、要素数は0、1、2です。

しかし、動的配列の場合は配列宣言時には要素数は指定せず、空の()にします。そして、実行途中で要素数を決めます。

今回のコードの場合、For文の中で要素数が決定されていくので、宣言時点では要素数は決めれません。

変数宣言時点


Dim Wd() As String

実行途中で要素数が決定


ReDim Preserve Wd(x) '配列

この場合、実行途中で要素数X=0が決定されます。そして、ループする毎にX=X+1で要素数が増加していきます。

Preserve

Preserveを付けることで、それまで配列に入っていた値は保持されます。逆にPreserveが無くRedimのみの場合それまで配列に入っていた値は失われます。

Preserveが無い場合

1が消えてしまいます。

Preserveなし
Preserveがある場合

1は残ります。

Preserveあり

まとめ

CurrentRegionを使用した表の選択や行、列の選択、その活用形について紹介しました。CurrentRegion自体はVBAでコードを書くにあたって使いやすいプロパティなので、是非参考にしてみてください!

次回も皆さんに有益なエクセル情報を紹介します!お楽しみに!

コメント

タイトルとURLをコピーしました